Faulty Quotes 3 – Contradictory Information

21 12 2009

December 21, 2009

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

Frustrated by some of the confusing and contradictory information I have encountered on the Internet over the years, I put together a “cheat sheet” to help identify useful information.  The “cheat sheet” lists several questions that one might consider when reviewing books and web articles prior to changing parameters (or implementing other changes) based on the information found in those sources:

  • Is a specific Oracle release mentioned in the book or article? What was true, or thought to be true, with release 8.0.5 might not be true or even a good idea with release 11.2.0.1.
  • Does the article have a publication date, and is there a revision history that identifies the date and what modifications were made to the article? Articles which change from one day to the next without knowing what changed, and why the article changed, are difficult to use as justification for changes to the initialization parameters.
  • Are there any articles by other authors on the Internet which agree with the author’s suggestions or sharply disagree with the author’s suggestions? If Oracle’s official documentation strongly disagrees with the contents of the article, which of the two sources are correct? Should the advice be deemed an over-generalization which worked as a fix for a one time problem that is now advertised as something all DBAs should do as a first step in performance tuning?
  • Is there reproducible evidence that supports the claims made? Or, is the majority of the justification similar to “I have seen it a 100 times” or “a DBA at a fortune 50 company said to do this” or “I have been doing this for 25 years, and you should too”?
  • Does the parameter actually control the behavior which it is purported to control, and are there any potential side effects from modifying the parameter?




Miscellaneous Metalink Performance Articles

21 12 2009

December 21, 2009

(Forward to the Next Post in the Series)

A couple months ago I scanned through Metalink looking for interesting articles.  I found a couple that seem to be well written, most with recent modification dates, that someone out there might enjoy reading when troubleshooting performance problems.  Hopefully, the documents still exist on the Metalink replacement.

  • Doc ID 233112.1 “START HERE – Diagnosing Query Tuning Problems” – basically a click to jump to the specific problem being experienced.
  • Doc ID 745216.1 “Query Performance Degradation – Upgrade Related – Recommended Actions” – a tree like structure for performance tuning.
  • Doc ID 398838.1 “FAQ: Query Tuning Frequently Asked Questions” – another tree like structure.
  • Doc ID 223806.1 “Query with Unchanged Execution Plan is Slower than Previously” – another tree like structure.
  • Doc ID 387394.1 “Query using Binds is Suddenly Slow”
  • Doc ID 604256.1 “Why is a Particular Query Slower on One Machine than Another?” – another tree like structure.
  • Doc ID 372431.1 “Troubleshooting: Tuning a New Query”
  • Doc ID 163563.1 “Troubleshooting: Advanced Query Tuning” – another tree like structure
  • Doc ID 122812.1 “How to Tune a Query that Cannot be Modified”
  • Doc ID 67522.1 “Diagnosing Why a Query is Not Using an Index”
  • Doc ID 69992.1 “Why is my hint ignored?”
  • Doc ID 163424.1 “How to Identify a Hot Block within the Buffer Cache”
  • Doc ID 223117.1 “Tuning I/O-related waits” – another tree structure
  • Doc ID 402983.1 “Database Performance FAQ” – mentions pstack, system state dumps, 10046 traces, AWR/Statspack
  • Doc ID 66484.1 “Which Optimizer is Being Used”
  • Doc ID 271196.1 “Automatic SQL Tuning – SQL Profiles”
  • Doc ID 276103.1 “Performance Tuning Using 10g Advisors and Manageability Features”
  • Doc ID 463288.1 “How to generate an outline with a good plan loaded into the shared_pool”
  • Doc ID 43718.1 “View: V$SESSION_WAIT Reference”




SORT_AREA_SIZE Affecting Sorts to Disk with PGA_AGGREGATE_TARGET Set?

20 12 2009

December 20, 2009

(Forward to the Follow-Up Post)

A couple of years ago I was under the impression that increasing the SORT_AREA_SIZE parameter could reduce the number of sorts to disk in Oracle 10g R2 with the PGA_AGGREGATE_TARGET parameter set, using dedicated server processes, and with the WORKAREA_SIZE_POLICY set to AUTO (yes, confirmed to be set in the session).  Why?  Because I saw it happen a couple of times when troubleshooting performance problems.  But, the sorts to disk still happened on occasion when there seemed to be no reason why Oracle would not be able to perform an optimal, in-memory workarea execution.

Thankfully, someone questioned my earlier testing that produced results which were inconsistent with the documentation.  I then attempted to produce a test case to prove myself wrong.  I set out to trigger a query to perform a sort to disk, and then by changing only the  SORT_AREA_SIZE parameter, have Oracle switch to perform an in-memory workarea execution for the query.  Once the test case produced that result, I attempted to identify the trigger that caused Oracle to switch.  The test case appears in the following Usenet thread:
http://groups.google.com/group/comp.databases.oracle.server/msg/e0b8bea300e45ae4

Line wrapping is a problem in the above thread, so I have reproduced the test case below:

I was able to produce a test case on the base patch of Oracle 10.2.0.2 on Win 32.  The following required roughly 10 hours of testing and analysis.  I built a new database instance with the following pfile:

############################################
aq_tm_processes=1
background_core_dump=partial
cluster_database=FALSE
compatible=10.2.0.1.0
control_files=("C:\oracle\OraData\LT\ctlLT01.ctl", "C:\oracle\flash_recovery_area\LT\ctlLT02.ctl")
control_file_record_keep_time=7
cursor_sharing=FORCE
cursor_space_for_time=true
db_block_size=8192
db_cache_advice=on
db_block_checking=false
db_block_checksum=typical
db_domain=world
db_files=200
db_file_multiblock_read_count=16
db_flashback_retention_target=1440
db_name=LT
db_keep_cache_size=400M
db_recycle_cache_size=10M
db_recovery_file_dest_size=14000M
db_recovery_file_dest=C:\oracle\flash_recovery_area
db_unique_name=LT
db_writer_processes=1
global_names=false
instance_name=LT
java_pool_size=1M
job_queue_processes=10
log_archive_format=arc%s_%r.%t
log_buffer=1048576
log_checkpoint_interval=65536
log_checkpoint_timeout=3600
log_checkpoints_to_alert=false
max_dump_file_size=202400
nls_language=american
nls_territory=america
O7_DICTIONARY_ACCESSIBILITY=TRUE
open_cursors=1000
open_links=4
optimizer_dynamic_sampling=2
optimizer_features_enable=10.1.0.4   #Needed to overcome ORA-600 on production DB
optimizer_index_caching=0
optimizer_index_cost_adj=100
optimizer_mode=ALL_ROWS
pga_aggregate_target=150M
plsql_code_type=INTERPRETED
processes=210
query_rewrite_enabled=FALSE
query_rewrite_integrity=TRUSTED
recyclebin=ON
remote_login_passwordfile=EXCLUSIVE
service_names=LT
sessions=236
session_cached_cursors=200
sga_max_size=1100M
sga_target=900M
star_transformation_enabled=FALSE
statistics_level=typical
timed_statistics=true
transactions=259
transactions_per_rollback_segment=5
undo_management=AUTO
undo_retention=1800
undo_tablespace=ROLLBACK_DATA
workarea_size_policy=auto
background_dump_dest=C:\oracle\product\10.2.0\admin\LT\bdump
core_dump_dest=C:\oracle\product\10.2.0\admin\LT\cdump
user_dump_dest=C:\oracle\product\10.2.0\admin\LT\udump
utl_file_dir=C:\oracle\product\10.2.0\admin\LT\udump
############################################

Create a new user named TESTING

Create a table for testing and fill in the table:

CREATE TABLE T1 (
  C1 VARCHAR2(20),
  C2 NUMBER(12),
  C3 NUMBER(12));

DECLARE
  X NUMBER;
BEGIN
  FOR X IN 1 .. 1000000
  LOOP
    INSERT INTO T1 VALUES (
      TO_CHAR(MOD( X ,100))||'-'||TO_CHAR(MOD( X ,100)),
      MOD( X ,500),
      X);
  END LOOP;
END;
/

COMMIT;

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

Let’s see where the sort statistics are right now:

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                   VALUE
----------------- ----------
sorts (disk)               0
sorts (memory)          2092
sorts (rows)            8342

Let’s turn on a 10053 trace so that we can see what the cost-based optimizer is willing to report:

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Run a simple SQL statement to generate a sort, and gather statistics for DBMS_XPLAN:

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

Determine the DBMS_XPLAN for the query:

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

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1004K|   1000K|00:00:04.19 |    2709 |   5627 |   2926 |    25M|  1843K|   30M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1004K|   1000K|00:00:00.02 |    2706 |   2701 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

This query used the temp tablespace as indicated by the Used-Tmp column.

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          43 Area size:      156672 Max Area size:     7864320
      Degree:               1
      Blocks to Sort:    3197 Row size:           26 Total Rows:        1004199
      Initial runs:         4 Merge passes:        1 IO Cost / pass:       1734
      Total IO sort cost: 4931      Total CPU sort cost: 992293034
      Total Temp space used: 48407000

PARAMETERS WITH DEFAULT VALUES
  _smm_min_size = 153 KB
  _smm_max_size = 30720 KB

Note:
 Max Area size is reported as 7,864,320 (7680 KB), while _smm_max_size is reported as 30720 KB.  On Oracle 10.2.0.3, when optimizer_features_enable is set to 10.2.0.3, both will be reported with the same values.
 5% of 150MB = 7.5MB = 7864320 bytes
 150MB/5 = 30MB = 31457280 bytes = 30720 KB

From my notes recorded during the second read through of “Cost-Based Oracle Fundamentals”:
sort width = same as the “max intermediate sort width” from a 10032 trace file
area size = amount of memory available for processing data – number reported will be smaller than the SORT_AREA_SIZE due to overhead
max area size = maximum memory available for sorting
degree = degree of parallelism for the query
blocks to sort = row_size*rows/db_block_size
row size = estimate of the average row size in bytes
rows = computed (filtered) cardinality of the table
initial runs = optimizer’s estimate of the number of sort runs that will be dumped to disk
merge passes = always at least one, even for an in-memory sort, counts the number of times the entire data set will be written to and read from disk in the event of a disk sort
IO cost/pass = cost of doing a single merge pass
total IO sort cost – combines the cost per pass with the number of passes
total CPU cost – CPU component of the cost – measured in CPU operations
total temp space used = estimated amount of temporary space needed for the sort operation

Let’s check the sort statistics:

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                    VALUE
------------------ ----------
sorts (disk)                1
sorts (memory)           3557
sorts (rows)          1015256

Let’s look at the plan statistics for the SQL statement:

SELECT
  SP.LAST_OUTPUT_ROWS,
  SP.TEMP_SPACE,
  SP.ESTIMATED_OPTIMAL_SIZE,
  SP.ESTIMATED_ONEPASS_SIZE,
  SP.LAST_MEMORY_USED,
  SP.LAST_EXECUTION,
  SP.LAST_TEMPSEG_SIZE
FROM
  V$SQL S,
  V$SQL_PLAN_STATISTICS_ALL SP
WHERE
  S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%'
  AND S.SQL_ID=SP.SQL_ID;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
         1000000   48407000               26966016                1887232         31472640 1 PASS                 23552

From the Oracle documentation:
TEMP_SPACE: Temporary space usage of the operation (sort or hash-join) as estimated by the optimizer’s cost-based approach. For statements that use the rule-based approach, this column is null.
ESTIMATED_OPTIMAL_SIZE: Estimated size (in KB) required by this work area to execute the operation completely in memory (optimal execution). This is either derived from optimizer statistics or from previous executions.
ESTIMATED_ONEPASS_SIZE: Estimated size (in KB) required by this work area to execute the operation in a single pass. This is either derived from optimizer statistics or from previous executions.
LAST_MEMORY_USED: Memory size (in KB) used by this work area during the last execution of the cursor
LAST_EXECUTION: Indicates whether this work area ran using OPTIMAL, ONE PASS, or under ONE PASS memory requirement (MULTI-PASS), during the last execution of the cursor
LAST_TEMPSEG_SIZE: Temporary segment size (in bytes) created in the last instantiation of this work area. This column is null if the last instantiation of this work area did not spill to disk.

The SQL statement required a 1 pass sort to disk.

Let’s try again, this time with a 200MB PGA_AGGREGATE_TARGET rather than a 150MB:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=200M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1004K|   1000K|00:00:01.82 |    2709 |   2926 |   2926 |    25M|  1843K|   30M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1004K|   1000K|00:00:00.01 |    2706 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

This query used the temp tablespace as indicated by the Used-Tmp column.

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          58 Area size:      208896 Max Area size:    10485760
      Degree:               1
      Blocks to Sort:    3197 Row size:           26 Total Rows:        1004199
      Initial runs:         3 Merge passes:        1 IO Cost / pass:       1734
      Total IO sort cost: 4931      Total CPU sort cost: 992293034
      Total Temp space used: 48407000

PARAMETERS WITH DEFAULT VALUES
  pga_aggregate_target = 204800 KB
  _smm_min_size        = 204 KB
  _smm_max_size        = 40960 KB

Note that the Area Size, Max Area Size, _smm_min_size, and _smm_max_size parameters increased, yet a sort to disk was still required.

Let’s check the sort statistics:

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                VALUE
-------------- ----------
sorts (disk)            2
sorts (memory)       4504
sorts (rows)      2022536

The sorts (disk) value increased again.

Let’s look at the plan statistics for the SQL statement:

SELECT
  SP.LAST_OUTPUT_ROWS,
  SP.TEMP_SPACE,
  SP.ESTIMATED_OPTIMAL_SIZE,
  SP.ESTIMATED_ONEPASS_SIZE,
  SP.LAST_MEMORY_USED,
  SP.LAST_EXECUTION,
  SP.LAST_TEMPSEG_SIZE
FROM
  V$SQL S,
  V$SQL_PLAN_STATISTICS_ALL SP
WHERE
  S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%'
  AND S.SQL_ID=SP.SQL_ID;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
         1000000   48407000               26966016                1887232         31472640 1 PASS                 23552

The SQL statement required a 1 pass sort to disk.

Since we are still sorting to disk, let’s increase the PGA_AGGREGATE_TARGET again and repeat the test:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=300M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1004K|   1000K|00:00:01.55 |    2706 |    34M|  2086K|   30M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1004K|   1000K|00:00:00.01 |    2706 |       |       |          |
----------------------------------------------------------------------------------------------------------------

The Used-Tmp column did not print, so no sort to disk was required.

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          88 Area size:      314368 Max Area size:    15728640
      Degree:               1
      Blocks to Sort:    3197 Row size:           26 Total Rows:        1004199
      Initial runs:         2 Merge passes:        1 IO Cost / pass:       1734
      Total IO sort cost: 4931      Total CPU sort cost: 992293034
      Total Temp space used: 48407000

PARAMETERS WITH DEFAULT VALUES
  pga_aggregate_target = 307200 KB
  _smm_min_size        = 307 KB
  _smm_max_size        = 61440 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                    VALUE
------------------ ----------
sorts (disk)                2
sorts (memory)           6223
sorts (rows)          3035178

As shown above, no sort to disk was needed with PGA_AGGREGATE_TARGET at 300MB, but a sort to disk was required at 200MB.

Let’s continue the test, dropping PGA_AGGREGATE_TARGET back to 200MB:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=200M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1004K|   1000K|00:00:01.55 |    2706 |    34M|  2086K|   30M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1004K|   1000K|00:00:00.01 |    2706 |       |       |          |
----------------------------------------------------------------------------------------------------------------

The Used-Tmp column did not print, so no sort to disk was required.  So, increasing the PGA_AGGREGATE_TARGET from 150MB to 200MB apparently forces a sort to disk, while decreasing the PGA_AGGREGATE_TARGET from 300MB to 200MB does not require a sort to disk.

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          58 Area size:      208896 Max Area size:    10485760
      Degree:               1
      Blocks to Sort:    3197 Row size:           26 Total Rows:        1004199
      Initial runs:         3 Merge passes:        1 IO Cost / pass:       1734
      Total IO sort cost: 4931      Total CPU sort cost: 992293034
      Total Temp space used: 48407000

PARAMETERS WITH DEFAULT VALUES
  pga_aggregate_target = 204800 KB
  _smm_min_size        = 204 KB
  _smm_max_size        = 40960 KB

The values are the same as for the first run with PGA_AGGREGATE_TARGET at 200MB.

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                   VALUE
----------------- ----------
sorts (disk)               2
sorts (memory)          7224
sorts (rows)         4043937

The sorts (disk) value did not increase.

SELECT
  SP.LAST_OUTPUT_ROWS,
  SP.TEMP_SPACE,
  SP.ESTIMATED_OPTIMAL_SIZE,
  SP.ESTIMATED_ONEPASS_SIZE,
  SP.LAST_MEMORY_USED,
  SP.LAST_EXECUTION,
  SP.LAST_TEMPSEG_SIZE
FROM
  V$SQL S,
  V$SQL_PLAN_STATISTICS_ALL SP
WHERE
  S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%'
  AND S.SQL_ID=SP.SQL_ID;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
         1000000   48407000               35688448                2136064         31722496 OPTIMAL

Note that LAST_TEMPSEG_SIZE is null in the above, and that the last execution was OPTIMAL.

Let’s drop the PGA_AGGREGATE_TARGET to 150MB and see if we have an optimal sort (no sort to disk):

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=150M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

(EXECUTED IN A DIFFERENT SESSION WHILE THE ABOVE IS EXECUTING):

SELECT
  *
FROM
  V$SORT_USAGE;

USERNAME USER     SESSION_SESSION_NUM SQLADDR     SQLHASH SQL_ID        TABLESPACE     CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#    EXTENTS     BLOCKS   SEGRFNO#
-------- -------- ------------------- -------- ---------- ------------- -------------- --------- --------- ---------- ---------- ---------- ---------- ----------
TESTING  TESTING  54C631C8          1 507DB66C 2019831986 4ww44m1w68c5k TEMPORARY_DATA1TEMPORARY SORT             201       2825         23       2944          1

(2944 blocks * 8KB block size = 24,117,248 bytes)

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

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1004K|   1000K|00:00:02.85 |    2709 |   2926 |   2926 |    25M|  1843K|   30M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1004K|   1000K|00:00:00.01 |    2706 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

This query used the temp tablespace as indicated by the Used-Tmp column.

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          43 Area size:      156672 Max Area size:     7864320
      Degree:               1
      Blocks to Sort:       1 Row size:           76 Total Rows:              1
      Initial runs:         1 Merge passes:        0 IO Cost / pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 11511282
      Total Temp space used: 0

PARAMETERS WITH DEFAULT VALUES
  pga_aggregate_target                = 153600 KB
  _smm_min_size                       = 153 KB
  _smm_max_size                       = 30720 KB

Problem in the 10053 trace file?  Note the “Total Temp space used: 0″

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                   VALUE
----------------- ----------
sorts (disk)               3
sorts (memory)          9997
sorts (rows)         5068937

The sorts (disk) value did increase.

SELECT
  SP.LAST_OUTPUT_ROWS,
  SP.TEMP_SPACE,
  SP.ESTIMATED_OPTIMAL_SIZE,
  SP.ESTIMATED_ONEPASS_SIZE,
  SP.LAST_MEMORY_USED,
  SP.LAST_EXECUTION,
  SP.LAST_TEMPSEG_SIZE
FROM
  V$SQL S,
  V$SQL_PLAN_STATISTICS_ALL SP
WHERE
  S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%'
  AND S.SQL_ID=SP.SQL_ID;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
         1000000   48407000               26966016                1887232         31472640 1 PASS                 23552

Let’s again increase the PGA_AGGREGATE_TARGET to 200MB and see if we have an optimal sort (no sort to disk):

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=200M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

(EXECUTED IN A DIFFERENT SESSION WHILE THE ABOVE IS EXECUTING):

SELECT
  *
FROM
  V$SORT_USAGE;

USERNAME USER     SESSION_SESSION_NUM SQLADDR     SQLHASH SQL_ID        TABLESPACE CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#    EXTENTS     BLOCKS   SEGRFNO#
-------- -------- ------------------- -------- ---------- ------------- -------------------------------  --------- --------- ---------- ---------- ----------
TESTING  TESTING  54C631C8          1 507DB66C 2019831986 4ww44m1w68c5k TEMPORARY_DATA1  TEMPORARY SORT       201          9         23       2944          1

The above shows a sort to disk in progress.

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

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1004K|   1000K|00:00:01.80 |    2709 |   2926 |   2926 |    25M|  1843K|   30M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1004K|   1000K|00:00:00.01 |    2706 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

The DBMS_XPLAN shows that a sort to disk was required.

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          58 Area size:      208896 Max Area size:    10485760
      Degree:               1
      Blocks to Sort:    3197 Row size:           26 Total Rows:        1004199
      Initial runs:         3 Merge passes:        1 IO Cost / pass:       1734
      Total IO sort cost: 4931      Total CPU sort cost: 992293034
      Total Temp space used: 48407000

PARAMETERS WITH DEFAULT VALUES
  pga_aggregate_target = 204800 KB
  _smm_min_size        = 204 KB
  _smm_max_size        = 40960 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                    VALUE
------------------ ----------
sorts (disk)                4
sorts (memory)          11776
sorts (rows)          6081798

The sorts (disk) value did increase.

SELECT
  SP.LAST_OUTPUT_ROWS,
  SP.TEMP_SPACE,
  SP.ESTIMATED_OPTIMAL_SIZE,
  SP.ESTIMATED_ONEPASS_SIZE,
  SP.LAST_MEMORY_USED,
  SP.LAST_EXECUTION,
  SP.LAST_TEMPSEG_SIZE
FROM
  V$SQL S,
  V$SQL_PLAN_STATISTICS_ALL SP
WHERE
  S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%'
  AND S.SQL_ID=SP.SQL_ID;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
         1000000   48407000               26966016                1887232         31472640 1 PASS                 23552

The above shows a 1 pass sort to disk was required.

Now, let’s see if changing the SORT_AREA_SIZE helps remove the sort to disk, as I suggested in my previous post:

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET SORT_AREA_SIZE=41943040;
ALTER SESSION SET SORT_AREA_RETAINED_SIZE=41943040;

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

(EXECUTED IN A DIFFERENT SESSION WHILE THE ABOVE IS EXECUTING):

SELECT
  *
FROM
  V$SORT_USAGE;

(No rows)

 

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

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1004K|   1000K|00:00:01.57 |    2706 |    34M|  2086K|   30M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1004K|   1000K|00:00:00.01 |    2706 |       |       |          |
----------------------------------------------------------------------------------------------------------------

No sort to disk was required.

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          58 Area size:      208896 Max Area size:    10485760
      Degree:               1
      Blocks to Sort:    3197 Row size:           26 Total Rows:        1004199
      Initial runs:         3 Merge passes:        1 IO Cost / pass:       1734
      Total IO sort cost: 4931      Total CPU sort cost: 992293034
      Total Temp space used: 48407000

PARAMETERS WITH ALTERED VALUES (for some reason, these did not show the first time the query was parsed - I had to force a second parse):
  sort_area_size                      = 41943040
  sort_area_retained_size             = 41943040

PARAMETERS WITH DEFAULT VALUES
  _smm_min_size                       = 204 KB
  _smm_max_size                       = 40960 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                    VALUE
------------------ ----------
sorts (disk)                4
sorts (memory)          12126
sorts (rows)          7084470

The sorts (disk) value did not increase.

SELECT
  SP.LAST_OUTPUT_ROWS,
  SP.TEMP_SPACE,
  SP.ESTIMATED_OPTIMAL_SIZE,
  SP.ESTIMATED_ONEPASS_SIZE,
  SP.LAST_MEMORY_USED,
  SP.LAST_EXECUTION,
  SP.LAST_TEMPSEG_SIZE
FROM
  V$SQL S,
  V$SQL_PLAN_STATISTICS_ALL SP
WHERE
  S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%'
  AND S.SQL_ID=SP.SQL_ID;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
          113401   48407000               35688448                2136064         31722496 OPTIMAL

Note that LAST_TEMPSEG_SIZE is null in the above, and that the last execution was OPTIMAL.  So, as I originally suggested, changing the SORT_AREA_SIZE for the session helped by removing the sort to disk.  Or not – I suspect the second time the query needed to be parsed, it would not have required a sort to disk.

—-

Now, let’s try a similar test on Oracle 10.2.0.3 with a pre-existing database that was started with the following parameters:

optimizer_features_enable=10.2.0.3
optimizer_index_caching=0
optimizer_index_cost_adj=100
pga_aggregate_target=300M
query_rewrite_enabled=FALSE
sga_max_size=1100M
sga_target=900M
sort_area_retained_size  not specified
sort_area_size  not specified
workarea_size_policy=auto

 

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Let’s first dump a DBMS_XPLAN with the estimated statistics.

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |       |  5302 (100)|          |
|   1 |  SORT ORDER BY     |      |  1000K|    13M|    46M|  5302   (6)| 00:00:29 |
|   2 |   TABLE ACCESS FULL| T1   |  1000K|    13M|       |   604   (9)| 00:00:04 |
-----------------------------------------------------------------------------------

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:         358 Area size:      314368 Max Area size:    62914560
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:         2 Merge passes:        1 IO Cost / pass:       1270
      Total IO sort cost: 4456      Total CPU sort cost: 981216297
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size = 307 KB
  _smm_max_size = 61440 KB    {= 62914560 bytes}

Note: 5% of 300MB = 15MB = 15728640 bytes,     300MB/5 = 60MB = 62,914,560

Oracle 10.2.0.3 with optimizer_features_enable set to 10.2.0.3 reports Max Area size at 62914560 bytes, which is identical to _smm_max_size, which is 1/5 of the pga_aggregate_target.  In the above, Oracle 10.2.0.2 reported Max Area size at roughly 5% of the pga_aggregate_target.

Let’s repeat, this time requesting the additional statistics from DBMS XPLAN:

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:03.57 |    2712 |    34M|  2086K|   30M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME              VALUE
-----------------------
sorts (disk)          0
sorts (memory)     4069
sorts (rows)    3027233

No sort to disk was required.

Repeat with pga_aggregate_target at 200MB:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=200M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:03.56 |    2712 |    34M|  2086K|   30M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:         238 Area size:      208896 Max Area size:    41943040
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:         2 Merge passes:        1 IO Cost / pass:       1270
      Total IO sort cost: 4456      Total CPU sort cost: 981216297
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size = 204 KB
  _smm_max_size = 40960 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                    VALUE
------------------ ----------
sorts (disk)                0
sorts (memory)           6283
sorts (rows)          4044290

No sort to disk required.

Repeat with pga_aggregate_target at 100MB:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=100M;
ALTER SYSTEM FLUSH SHARED_POOL;

 

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:07.21 |    2717 |   2928 |   2928 |    25M|  1843K|   20M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

 

From 10053 trace:

    SORT resource      Sort statistics
      Sort width:         118 Area size:      131072 Max Area size:    20971520
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:         2 Merge passes:        1 IO Cost / pass:       1270
      Total IO sort cost: 4456      Total CPU sort cost: 981216297
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size = 128 KB
  _smm_max_size = 20480 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                        VALUE
---------------------- ----------
sorts (disk)                    1
sorts (memory)               7785
sorts (rows)              5058041

This time, a sort to disk was required.

Let’s try again, this time bumping the SORT_AREA_SIZE to roughly 40MB:

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET SORT_AREA_SIZE=41943040;
ALTER SESSION SET SORT_AREA_RETAINED_SIZE=41943040;

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:04.34 |    2717 |   2928 |   2928 |    25M|  1843K|   20M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

 

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:         118 Area size:      131072 Max Area size:    20971520
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:         2 Merge passes:        1 IO Cost / pass:       1270
      Total IO sort cost: 4456      Total CPU sort cost: 981216297
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size                       = 128 KB
  _smm_max_size                       = 20480 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                        VALUE
---------------------- ----------
sorts (disk)                    2
sorts (memory)               9699
sorts (rows)              6073631

A sort to disk was still required, so changing SORT_AREA_SIZE did not help – of course the SORT_AREA_SIZE would have been double the value of _smm_max_size…

Let’s try again, this time using the optimizer setting for Oracle 10.2.0.2:

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.2.0.2';

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:04.36 |    2717 |   2928 |   2928 |    25M|  1843K|   20M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

 

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:         118 Area size:      131072 Max Area size:    20971520
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:         2 Merge passes:        1 IO Cost / pass:       1270
      Total IO sort cost: 4456      Total CPU sort cost: 981216297
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size = 128 KB
  _smm_max_size = 20480 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                        VALUE
---------------------- ----------
sorts (disk)                    3
sorts (memory)              11435
sorts (rows)              7088591

Still a sort to disk.

Let’s try again, this time using the optimizer setting for Oracle 10.1.0.4:

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.1.0.4';

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:04.30 |    2717 |   2928 |   2928 |    25M|  1843K|   20M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          28 Area size:      131072 Max Area size:     5242880
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:         5 Merge passes:        1 IO Cost / pass:       1270
      Total IO sort cost: 4456      Total CPU sort cost: 981216297
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size = 128 KB
  _smm_max_size = 20480 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                   VALUE
----------------- ----------
sorts (disk)               4
sorts (memory)         12907
sorts (rows)         8102004

Still a sort to disk, but note that Max Area size no longer matches the value for _smm_max_size.

Let’s try again with pga_aggregate_target at 300MB:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=300M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

----------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:03.58 |    2712 |    34M|  2086K|   30M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          88 Area size:      314368 Max Area size:    15728640
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:         2 Merge passes:        1 IO Cost / pass:       1270
      Total IO sort cost: 4456      Total CPU sort cost: 981216297
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size = 307 KB
  _smm_max_size = 61440 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                   VALUE
----------------- ----------
sorts (disk)               4
sorts (memory)         14532
sorts (rows)         9116171

No sort to disk, but SORT_AREA_SIZE is still set to roughly 40MB.

Try again, reseting SORT_AREA_SIZE:

ALTER SESSION SET SORT_AREA_SIZE=65536;
ALTER SESSION SET SORT_AREA_RETAINED_SIZE=0;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:03.56 |    2712 |    34M|  2086K|   30M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          88 Area size:      314368 Max Area size:    15728640
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:         2 Merge passes:        1 IO Cost / pass:       1270
      Total IO sort cost: 4456      Total CPU sort cost: 981216297
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size = 307 KB
  _smm_max_size = 61440 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                  VALUE
---------------- ----------
sorts (disk)              4
sorts (memory)        16400
sorts (rows)       10132013

No sort to disk, changing SORT_AREA_SIZE did not change anything.

Just to confirm, in a new session:

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='10.1.0.4';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:03.56 |    2712 |    34M|  2086K|   30M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          88 Area size:      314368 Max Area size:    15728640
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:         2 Merge passes:        1 IO Cost / pass:       1270
      Total IO sort cost: 4456      Total CPU sort cost: 981216297
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size = 307 KB
  _smm_max_size = 61440 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                VALUE
-------------- ----------
sorts (disk)            4
sorts (memory)      17859
sorts (rows)     11145435

No sort to disk with no SORT_AREA_SIZE specified.

Try again, setting PGA_AGGREGATE_TARGET back to 200MB:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=200M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:03.56 |    2712 |    34M|  2086K|   30M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          58 Area size:      208896 Max Area size:    10485760
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:         3 Merge passes:        1 IO Cost / pass:       1270
      Total IO sort cost: 4456      Total CPU sort cost: 981216297
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size = 204 KB
  _smm_max_size = 40960 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

-------------- ----------
sorts (disk)            4
sorts (memory)      19443
sorts (rows)     12159195

Still no sort to disk.

SELECT
  SP.LAST_OUTPUT_ROWS,
  SP.TEMP_SPACE,
  SP.ESTIMATED_OPTIMAL_SIZE,
  SP.ESTIMATED_ONEPASS_SIZE,
  SP.LAST_MEMORY_USED,
  SP.LAST_EXECUTION,
  SP.LAST_TEMPSEG_SIZE
FROM
  V$SQL S,
  V$SQL_PLAN_STATISTICS_ALL SP
WHERE
  S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%'
  AND S.SQL_ID=SP.SQL_ID;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
         1000000   48243000               35688448                2136064         31722496 OPTIMAL  

This SQL statement used an optimal sort (no sort to disk).

Trying again with PGA_AGGREGATE_TARGET at 100MB:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=100M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

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

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:04.30 |    2717 |   2928 |   2928 |    25M|  1843K|   20M (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:          28 Area size:      131072 Max Area size:     5242880
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:         5 Merge passes:        1 IO Cost / pass:       1270
      Total IO sort cost: 4456      Total CPU sort cost: 981216297
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size  = 128 KB
  _smm_max_size  = 20480 KB

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                VALUE
-------------------------
sorts (disk)            5
sorts (memory)      27009
sorts (rows)     14227511

A sort to disk was required.

SELECT
  SP.LAST_OUTPUT_ROWS,
  SP.TEMP_SPACE,
  SP.ESTIMATED_OPTIMAL_SIZE,
  SP.ESTIMATED_ONEPASS_SIZE,
  SP.LAST_MEMORY_USED,
  SP.LAST_EXECUTION,
  SP.LAST_TEMPSEG_SIZE
FROM
  V$SQL S,
  V$SQL_PLAN_STATISTICS_ALL SP
WHERE
  S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%'
  AND S.SQL_ID=SP.SQL_ID;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
         1000000   48243000               26984448                1887232         21013504 1 PASS                 23552

A 1 pass sort to disk was required.

Let’s be cruel to Oracle to see what happens with PGA_AGGREGATE_TARGET at 20MB:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=20M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

(Executed from a different session while the above was running):

SELECT
  *
FROM
  V$SORT_USAGE;

USERNAME USER     SESSION_SESSION_NUM SQLADDR     SQLHASH SQL_ID        TABLESPACE CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#   EXTENTS     BLOCKS   SEGRFNO#
-------- -------- ------------------- -------- ---------- ------------- ---------------------------------------- --------- ---------- ---------- ----------
TESTING  TESTING  54C654DC         70 50FD66AC  580783698 98h83a8j9w3kk TEMPORARY_DATA1 TEMPORARY SORT        201     895625         23      2944          1

 

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

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:12.33 |    2730 |   2931 |   2931 |    25M|  1844K| 4141K (1)|   23552 |
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------

From 10053 trace:

ORDER BY sort
    SORT resource      Sort statistics
      Sort width:           4 Area size:      131072 Max Area size:     1048576
      Degree:               1
      Blocks to Sort:    3186 Row size:           26 Total Rows:        1000729
      Initial runs:        25 Merge passes:        3 IO Cost / pass:       1270
      Total IO sort cost: 6996      Total CPU sort cost: 1059674733
      Total Temp space used: 48243000

Under PARAMETERS WITH DEFAULT VALUES:
  _smm_min_size                       = 128 KB
  _smm_max_size                       = 4096 KB

The execution plan shows a sort to disk was required.

SELECT
  SP.LAST_OUTPUT_ROWS,
  SP.TEMP_SPACE,
  SP.ESTIMATED_OPTIMAL_SIZE,
  SP.ESTIMATED_ONEPASS_SIZE,
  SP.LAST_MEMORY_USED,
  SP.LAST_EXECUTION,
  SP.LAST_TEMPSEG_SIZE
FROM
  V$SQL S,
  V$SQL_PLAN_STATISTICS_ALL SP
WHERE
  S.SQL_TEXT LIKE 'SELECT /*+ GATHER_PLAN_STATISTICS */%'
  AND S.SQL_ID=SP.SQL_ID;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
         1000000   48243000               27012096                1888256          4240384 1 PASS                 23552

Still a 1 pass sort to disk.

Let’s see the hidden parameters (adapted from a script on Jonathan Lewis’ website):

SELECT
  UPPER(NAM.KSPPINM) NAME,
  VAL.KSPPSTVL VALUE,
  VAL.KSPPSTDF ISDEFAULT,
  DECODE(BITAND(VAL.KSPPSTVF,7),
    1,'MODIFIED',
    4,'SYSTEM MODIFIED',
    'FALSE') ISMODIFIED
FROM
  X$KSPPI NAM,
  X$KSPPSV VAL
WHERE
  NAM.INDX = VAL.INDX
  AND UPPER(NAM.KSPPINM) IN ('_SMM_MIN_SIZE','_SMM_MAX_SIZE')
ORDER BY
  UPPER(NAM.KSPPINM);

NAME            VALUE        ISDEFAULT ISMODIFIED
-------- ------------------------------------------
_SMM_MAX_SIZE   4096         TRUE      FALSE
_SMM_MIN_SIZE    128         TRUE      FALSE

—-

I performed a similar test on Oracle 10.2.0.2 with the July 2006 CPU on Win x64, and the results were similar to those of Oracle 10.2.0.3.

In summary, as the “Oracle Database Performance Tuning Guide 10g Release 2″ Pg 7-38 (PDF page 146) documentation states, “sizing of work areas for all sessions becomes automatic and the *_AREA_SIZE parameters are ignored by all sessions running in that mode.”  There is apparently an odd quirk that once in a while, the first time a SQL statement is parsed, a sort to disk may be required, at least under the base patch of Oracle 10.2.0.2.  This lead me, incorrectly, to believe that setting the SORT_AREA_SIZE to a larger value and re-executing the query actually removed the sort to disk – but it was actually the second parse that resulted in the removal of the sort to disk.  This test case disproves my suggestion that the SORT_AREA_SIZE has any impact on Oracle 10.2.0.2 when all sessions are set to auto for the WORKAREA_SIZE_POLICY.  It is possible to modify the WORKAREA_SIZE_POLICY at the session level, and then the SORT_AREA_SIZE setting takes effect for that session.

This displays the optimizer’s expected execution statistics:

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

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |       |  5302 (100)|          |
|   1 |  SORT ORDER BY     |      |  1000K|    13M|    46M|  5302   (6)| 00:00:29 |
|   2 |   TABLE ACCESS FULL| T1   |  1000K|    13M|       |   604   (9)| 00:00:04 |
-----------------------------------------------------------------------------------

This displays the actual execution statistics for the last run:

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

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY     |      |      1 |   1000K|   1000K|00:00:03.57 |    2712 |    34M|  2086K|   30M (0)|
|   2 |   TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:02.00 |    2712 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Here is a new test run on Oracle 10.2.0.2 July 2006 CPU Win x64.  I did not run any explain plans during this test, although I did enable a 10053 trace:

First test at 150MB:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=150M;
ALTER SYSTEM FLUSH SHARED_POOL;

Current sort statistics:

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                    VALUE
------------------ ----------
sorts (disk)               87
sorts (memory)       28169866
sorts (rows)        675027667

No other sorts in process – an otherwise idle database instance:

SELECT
  *
FROM
  V$SORT_USAGE;

no rows selected

Run the query:

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

SELECT /* FIND_ME */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

(EXECUTED IN A DIFFERENT SESSION WHILE THE ABOVE IS EXECUTING):

SELECT
  *
FROM
  V$SORT_USAGE;

USERNAME USER     SESSION_ADDR     SESSION_NUM SQLADDR             SQLHASH SQL_ID        TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#     EXTENTS     BLOCKS   SEGRFNO#
-------- -------- ---------------- ----------- ---------------- ---------- ------------- ------------------------------- --------- --------- ---------- ---------- ---------- ---------- ----------
TESTING  TESTING  000007FF9E4C0160       61714 000007FF929494C8 1262288602 0gzdjb55mtzqu TEMPORARY_DATA1                 TEMPORARY SORT             201      37641         23       2944          1

The above shows that the query is actively sorting to the temp tablespace.

The sorts (disk) statistic increased:

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                  VALUE
---------------- ----------
sorts (disk)             88
sorts (memory)     28170875
sorts (rows)      676034807

The execution statistics for the plan shows the estimates and actuals.  A 1 pass sort to disk:

SELECT
  SP.LAST_OUTPUT_ROWS,
  SP.TEMP_SPACE,
  SP.ESTIMATED_OPTIMAL_SIZE,
  SP.ESTIMATED_ONEPASS_SIZE,
  SP.LAST_MEMORY_USED,
  SP.LAST_EXECUTION,
  SP.LAST_TEMPSEG_SIZE
FROM
  V$SQL S,
  V$SQL_PLAN_STATISTICS_ALL SP
WHERE
  S.SQL_TEXT LIKE 'SELECT /* FIND_ME */%'
  AND S.SQL_ID=SP.SQL_ID;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
                   48522000               26975232                1887232         31472640 1 PASS                 23552

Second test at 200MB:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=200M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /* FIND_ME */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

(EXECUTED IN A DIFFERENT SESSION WHILE THE ABOVE IS EXECUTING):

SELECT
  *
FROM
  V$SORT_USAGE;

USERNAME USER     SESSION_ADDR     SESSION_NUM SQLADDR             SQLHASH SQL_ID        TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#     EXTENTS     BLOCKS   SEGRFNO#
-------- -------- ---------------- ----------- ---------------- ---------- ------------- ------------------------------- --------- --------- ---------- ---------- ---------- ---------- ----------
TESTING  TESTING  000007FF9E4C0160       61714 000007FF94EA7488 2019831986 4ww44m1w68c5k TEMPORARY_DATA1                 TEMPORARY SORT             201      29705        23       2944          1

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                VALUE
-------------- ----------
sorts (disk)           89
sorts (memory)   28172666
sorts (rows)    677048890

The execution statistics for the plan shows the estimates and actuals.  A 1 pass sort to disk:

SELECT
  SP.LAST_OUTPUT_ROWS,
  SP.TEMP_SPACE,
  SP.ESTIMATED_OPTIMAL_SIZE,
  SP.ESTIMATED_ONEPASS_SIZE,
  SP.LAST_MEMORY_USED,
  SP.LAST_EXECUTION,
  SP.LAST_TEMPSEG_SIZE
FROM
  V$SQL S,
  V$SQL_PLAN_STATISTICS_ALL SP
WHERE
  S.SQL_TEXT LIKE 'SELECT /* FIND_ME */%'
  AND S.SQL_ID=SP.SQL_ID;

The execution statistics for the plan shows the estimates and actuals.  A 1 pass sort to disk the first time this query was parsed with PGA_AGGREGATE_TARGET=200M:

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
                   48522000               26975232                1887232         31472640 1 PASS                 23552

Third test at 200MB (note changing SORT_AREA_SIZE is not the cause of the sort to disk disappearing):

ALTER SESSION SET SORT_AREA_SIZE=41943040;
ALTER SESSION SET SORT_AREA_RETAINED_SIZE=41943040;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /* FIND_ME */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

(EXECUTED IN A DIFFERENT SESSION WHILE THE ABOVE IS EXECUTING):

SELECT
  *
FROM
  V$SORT_USAGE;

no rows selected

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                VALUE
-------------- ----------
sorts (disk)           89
sorts (memory)   28173729
sorts (rows)    678058329

SELECT
  SP.LAST_OUTPUT_ROWS,
  SP.TEMP_SPACE,
  SP.ESTIMATED_OPTIMAL_SIZE,
  SP.ESTIMATED_ONEPASS_SIZE,
  SP.LAST_MEMORY_USED,
  SP.LAST_EXECUTION,
  SP.LAST_TEMPSEG_SIZE
FROM
  V$SQL S,
  V$SQL_PLAN_STATISTICS_ALL SP
WHERE
  S.SQL_TEXT LIKE 'SELECT /* FIND_ME */%'
  AND S.SQL_ID=SP.SQL_ID;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
                   48522000               40551424                2262016         36044800 OPTIMAL

Fourth test at 300MB:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=300M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /* FIND_ME */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

(EXECUTED IN A DIFFERENT SESSION WHILE THE ABOVE IS EXECUTING):

SELECT
  *
FROM
  V$SORT_USAGE;

no rows selected

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                VALUE
-------------- ----------
sorts (disk)           89
sorts (memory)   28175547
sorts (rows)    679074619

SELECT
  SP.LAST_OUTPUT_ROWS,
  SP.TEMP_SPACE,
  SP.ESTIMATED_OPTIMAL_SIZE,
  SP.ESTIMATED_ONEPASS_SIZE,
  SP.LAST_MEMORY_USED,
  SP.LAST_EXECUTION,
  SP.LAST_TEMPSEG_SIZE
FROM
  V$SQL S,
  V$SQL_PLAN_STATISTICS_ALL SP
WHERE
  S.SQL_TEXT LIKE 'SELECT /* FIND_ME */%'
  AND S.SQL_ID=SP.SQL_ID;

LAST_OUTPUT_ROWS TEMP_SPACE ESTIMATED_OPTIMAL_SIZE ESTIMATED_ONEPASS_SIZE LAST_MEMORY_USED LAST_EXECU LAST_TEMPSEG_SIZE
---------------- ---------- ---------------------- ---------------------- ---------------- ---------- -----------------
                   48522000               40551424                2262016         36044800 OPTIMAL

Fifth test at 150MB:

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=150M;
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT /* FIND_ME */
  *
FROM
  T1
ORDER BY
  C2,
  C3;

(EXECUTED IN A DIFFERENT SESSION WHILE THE ABOVE IS EXECUTING):

SELECT
  *
FROM
  V$SORT_USAGE;

USERNAME USER     SESSION_ADDR     SESSION_NUM SQLADDR             SQLHASH SQL_ID        TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#     EXTENTS     BLOCKS   SEGRFNO#
-------- -------- ---------------- ----------- ---------------- ---------- ------------- ------------------------------- --------- --------- ---------- ---------- ---------- ---------- ----------
TESTING  TESTING  000007FF9E4C0160       61714 000007FF94EA7488 2019831986 4ww44m1w68c5k TEMPORARY_DATA1                 TEMPORARY SORT             201      37641         23       2944          1

 

SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
WHERE
  UPPER(NAME) LIKE '%SORT%'
ORDER BY
  NAME;

NAME                VALUE
-------------- ----------
sorts (disk)           89
sorts (memory)   28172666
sorts (rows)    677048890

SELECT
  SP.LAST_OUTPUT_ROWS,
  SP.TEMP_SPACE,
  SP.ESTIMATED_OPTIMAL_SIZE,
  SP.ESTIMATED_ONEPASS_SIZE,
  SP.LAST_MEMORY_USED,
  SP.LAST_EXECUTION,
  SP.LAST_TEMPSEG_SIZE
FROM
  V$SQL S,
  V$SQL_PLAN_STATISTICS_ALL SP
WHERE
  S.SQL_TEXT LIKE 'SELECT /* FIND_ME */%'
  AND S.SQL_ID=SP.SQL_ID;

 

It might be interesting to see how the test behaves on Oracle database 11.1.0.7 or 11.2.o.1.





Database Inpector Gadget

19 12 2009

December 19, 2009

This example is adapted from an example I created for a presentation a  couple months ago.  While the example as displayed is specific to displaying ERP data, the example may be easily adapted for use in monitoring Oracle database performance.

Vista and Windows 7 offer a set of built in gadgets that perform a variety of tasks, such as displaying calendars, clocks, resource meters, stock quotes, and so on.  It might be nice to show a quick overview of various statistics in an ERP system (or in the database itself) so that a determination may be made whether or not a potential problem exists.  Such a gadget would need to be able to automatically update its statistics.  This example is complicated as it combines a large number of technologies each with their own unique syntax which looks vaguely like English.  This example uses VBScript, Oracle database access, cascading style sheets, XML, HTML, DHTML, and coding that is specific to Vista/Windows 7 gadgets.

When the gadget is opened, it determines the statistics that should be displayed for the user, and the order in which the statistics should be displayed.  The selected statistics are then retrieved from the database and displayed on the gadget surface in a DIV tag.  Every ten minutes the gadget reconnects to the database and displays updated statistics.  As the mouse pointer is moved over the gadget, the gadget’s background lights up slightly, and as the mouse pointer passes over the statistics, those also light up (more specifically, the background image for the statistic is changed).  When one of the statistics is clicked, a fly-out child window appears on the screen that shows the detailed statistics behind the one line numeric statistic.

The gadget code files should be placed into the C:\Program Files\Windows Sidebar\Gadgets\KMInspector.gadget folder on the computer (note that KMInspector may be specified with a different name).  The gadget.xml file in that folder describes the gadget to Vista/Windows 7 and tells Windows where to find the main gadget HTML file, the name of the gadget, and the name of the various icons – this file MUST be saved in a UTF-8 characterset (this is an option when saving with Notepad).  The KMInspector.html file is the main gadget page, and the various pages with names beginning with FlyOut are the pages containing the detail information.  The security requirement that all Windows gadgets must be signed needs to be disabled.

Below are a couple of screen shots of what we are trying to achieve (a standard Windows sidebar calendar gadget appears above, with the custom developed gadget below):
  

 

The top left screen shot shows the custom gadget directly below a built-in Windows calendar gadget.  The top right screen shot shows what happens when the mouse pointer passes over the gadget (note that the background becomes lighter in color, and the button under the mouse pointer changes color).  The bottom picture shows what happens when one of the buttons in the gedget is clicked.

gadget.xml file (save as in the UTF-8 character set):

<?xml version="1.0" encoding="utf-8" ?>
<gadget>
  <name>KM Inspector</name>
  <version>1.0.0.0</version>
  <icons>
    <icon height="48" width="36" src="InspectorIcon.png" />
  </icons>
  <hosts>
    <host name="sidebar">
      <base type="HTML" apiVersion="1.0.0" src="KMInspector.html" />
      <permissions>Full</permissions>
      <platform minPlatformVersion="1.0" />
      <defaultImage src="Inspector.png" />
    </host>
  </hosts>
</gadget>

Inspector.png is the picture that is displayed when the gadget is dragged from the list of available Windows gadgets to the sidebar.  InspectorIcon.png is the picture of the gadget that is displayed in the list of available Windows gadgets.  KMInspector.html is the web page that contains the code for the gadget.

Inspector.png:

InspectorIcon.png:

KMInspector.html file:

<html>
<head>
    <title>Visual Inspector</title>
    <style type="text/css">
        body
        {
            margin: 0px;
            width: 140px;
            height: 200px;
        }
        #gadgetContent
        {
            margin-top: 0px;
            width: 140px;
            vertical-align: middle;
            text-align: center;
            overflow: hidden;
        }
        .Item {
            background-position: center center;
            padding: 0px;
            font-family: Arial;
            font-size: 8pt;
            color: #FFFFFF;
            border-style: none;
            clip: width: 130px;
            height: 14px;
            position: absolute;
            left: 10px;
            width: 128px;
            background-image: url('InspectorButtonUp.png');
            background-repeat: no-repeat;
         }
        </style>
</head>
<script language="VBScript">
Option Explicit
Dim intMinuteCount       'Number of minutes that have elapsed since the last refresh
Dim intRefreshMinutes    'Frequency of the data refresh, in minutes
Dim strInpector(10)      'Keeps track of which data is displayed in each Inspector position
Dim userEntry
Sub Window_Onload
    Dim lngTimerID
    'Specify the Inspector items to be output, in the order that they should appear
    'Currently Available options
    '  "Negative On Hand"
    '  "Past Due PO Line Count"
    '  "In Use Shop Resources"
    '  "Employees Clocked In"
    '  "Employees Clocked Into Indirect"
    '  "Non Invoiced Shipper"

    strInpector(1) = "Negative On Hand"
    strInpector(2) = "Past Due PO Line Count"
    strInpector(3) = "In Use Shop Resources"
    strInpector(4) = "Employees Clocked In"
    strInpector(5) = "Employees Clocked Into Indirect"
    strInpector(6) = "Non Invoiced Shipper"
    strInpector(7) = ""
    strInpector(8) = ""
    strInpector(9) = ""
    strInpector(10) = ""

    'Hide those Inspector items which are not specified (set = "")
    If strInpector(1) = "" Then
        divInspector1.style.visibility = "hidden"
    End If
    If strInpector(2) = "" Then
        divInspector2.style.visibility = "hidden"
    End If
    If strInpector(3) = "" Then
        divInspector3.style.visibility = "hidden"
    End If
    If strInpector(4) = "" Then
        divInspector4.style.visibility = "hidden"
    End If
    If strInpector(5) = "" Then
        divInspector5.style.visibility = "hidden"
    End If
    If strInpector(6) = "" Then
        divInspector6.style.visibility = "hidden"
    End If
    If strInpector(7) = "" Then
        divInspector7.style.visibility = "hidden"
    End If
    If strInpector(8) = "" Then
        divInspector8.style.visibility = "hidden"
    End If
    If strInpector(9) = "" Then
        divInspector9.style.visibility = "hidden"
    End If
    If strInpector(10) = "" Then
        divInspector10.style.visibility = "hidden"
    End If

    'System.Gadget.onSettingsClosing = "SaveSettings"
    'System.Gadget.onSettingsClosed = "SaveSettingsClosed"
    System.Gadget.settingsUI = "settings.html"

    'Set the refresh frequency in minutes
    intRefreshMinutes = 10

    'Force a refresh on the first execution
    intMinuteCount = intRefreshMinutes

    'Refresh the Inspector items
    RefreshInspector

    'Set a times that fires every 60 seconds that determines if it is time to refresh the data
    lngTimerID = window.SetInterval("RefreshInspector", 60000)
End Sub

Sub RefreshInspector
    Dim i
    Dim lngCount
    Dim dbMyConnection
    Dim comData
    Dim snpData
    Dim strUsername
    Dim strPassword
    Dim strDatabase
    Dim strSQL
    Dim strOut

    On Error Resume Next

    'See if the specified number of minutes have elapsed since the last refresh, if not, exit the subroutine
    intMinuteCount = intMinuteCount + 1
    If intMinuteCount < intRefreshMinutes Then
        Exit Sub
    End If

    intMinuteCount = 0

    Set snpData = CreateObject("ADODB.Recordset")
    Set dbMyConnection = CreateObject("ADODB.Connection")

    strUsername = "MyUser"
    'Note that Switch is a function that implments a simple light-weight reversible encryption so that the password does not appear in clear text
    strPassword = Switch(Chr(112) & Chr(100) & Chr(15) & Chr(10) & Chr(180) & Chr(7) & Chr(206) & Chr(233) & Chr(25))
    strDatabase = "MyDB"

    dbMyConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
    dbMyConnection.Open

    If dbMyConnection.State = 1 Then
        For i = 1 to 10
            strOut = ""

            Select Case strInpector(i)
                Case "Negative On Hand"
                    strSQL = "SELECT" & VBCrLf
                    strSQL = strSQL & "  COUNT(*) AS NUM" & VBCrLf
                    strSQL = strSQL & "FROM" & VBCrLf
                    strSQL = strSQL & "  PART" & VBCrLf
                    strSQL = strSQL & "WHERE" & VBCrLf
                    strSQL = strSQL & "  QTY_ON_HAND<0"
                    snpData.Open strSQL, dbMyConnection

                    If snpData.State = 1 Then
                        If Not(snpData.EOF) Then
                            strOut = "Negative On Hand: " & cStr(snpData("num"))
                        End If
                        snpData.Close
                    End If
                Case "Past Due PO Line Count"
                    strSQL = "SELECT" & VBCrLf
                    strSQL = strSQL & "  COUNT(*) AS NUM" & VBCrLf
                    strSQL = strSQL & "FROM" & VBCrLf
                    strSQL = strSQL & "  PURCHASE_ORDER PO," & VBCrLf
                    strSQL = strSQL & "  PURC_ORDER_LINE POL," & VBCrLf
                    strSQL = strSQL & "  PURC_LINE_DEL PLD" & VBCrLf
                    strSQL = strSQL & "WHERE" & VBCrLf
                    strSQL = strSQL & "  PO.STATUS IN ('F','R','U')" & VBCrLf
                    strSQL = strSQL & "  AND PO.ID=POL.PURC_ORDER_ID" & VBCrLf
                    strSQL = strSQL & "  AND POL.LINE_STATUS='A'" & VBCrLf
                    strSQL = strSQL & "  AND POL.SERVICE_ID IS NULL" & VBCrLf
                    strSQL = strSQL & "  AND POL.ORDER_QTY>POL.TOTAL_RECEIVED_QTY" & VBCrLf
                    strSQL = strSQL & "  AND POL.PURC_ORDER_ID=PLD.PURC_ORDER_ID(+)" & VBCrLf
                    strSQL = strSQL & "  AND POL.LINE_NO=PLD.PURC_ORDER_LINE_NO(+)" & VBCrLf
                    strSQL = strSQL & "  AND DECODE(PLD.PURC_ORDER_LINE_NO,NULL,NVL(POL.ORDER_QTY,0),NVL(PLD.ORDER_QTY,0)) > " & VBCrLf
                    strSQL = strSQL & "     DECODE(PLD.PURC_ORDER_LINE_NO,NULL,NVL(TOTAL_RECEIVED_QTY,0),NVL(PLD.RECEIVED_QTY,0))" & VBCrLf
                    strSQL = strSQL & "  AND COALESCE(PLD.DESIRED_RECV_DATE,POL.DESIRED_RECV_DATE,PO.DESIRED_RECV_DATE) < SYSDATE"
                    snpData.Open strSQL, dbMyConnection

                    If snpData.State = 1 Then
                        If Not(snpData.EOF) Then
                            strOut = "PO Past Due: " & cStr(snpData("num"))
                        End If
                        snpData.Close
                    End If
                Case "In Use Shop Resources"
                    strSQL = "SELECT" & VBCrLf
                    strSQL = strSQL & "  COUNT(DISTINCT RESOURCE_ID) AS NUM" & VBCrLf
                    strSQL = strSQL & "FROM" & VBCrLf
                    strSQL = strSQL & "  LABOR_TICKET" & VBCrLf
                    strSQL = strSQL & "WHERE" & VBCrLf
                    strSQL = strSQL & "  TRANSACTION_DATE>=TRUNC(SYSDATE-180)" & VBCrLf
                    strSQL = strSQL & "  AND HOURS_WORKED IS NULL" & VBCrLf
                    strSQL = strSQL & "  AND RESOURCE_ID IS NOT NULL" & VBCrLf
                    strSQL = strSQL & "ORDER BY" & VBCrLf
                    strSQL = strSQL & "  RESOURCE_ID"
                    snpData.Open strSQL, dbMyConnection

                    If snpData.State = 1 Then
                        If Not(snpData.EOF) Then
                            strOut = "Resources in Use: " & cStr(snpData("num"))
                        End If
                        snpData.Close
                    End If
                Case "Employees Clocked In"
                    strSQL = "SELECT" & VBCrLf
                    strSQL = strSQL & "  COUNT(DISTINCT EMPLOYEE_ID) AS NUM" & VBCrLf
                    strSQL = strSQL & "FROM" & VBCrLf
                    strSQL = strSQL & "  LABOR_TICKET" & VBCrLf
                    strSQL = strSQL & "WHERE" & VBCrLf
                    strSQL = strSQL & "  TRANSACTION_DATE>=TRUNC(SYSDATE-180)" & VBCrLf
                    strSQL = strSQL & "  AND HOURS_WORKED IS NULL"
                    snpData.Open strSQL, dbMyConnection

                    If snpData.State = 1 Then
                        If Not(snpData.EOF) Then
                            strOut = "Emp Clocked In: " & cStr(snpData("num"))
                        End If
                        snpData.Close
                    End If
                Case "Employees Clocked Into Indirect"
                    strSQL = "SELECT" & VBCrLf
                    strSQL = strSQL & "  COUNT(DISTINCT EMPLOYEE_ID) AS NUM" & VBCrLf
                    strSQL = strSQL & "FROM" & VBCrLf
                    strSQL = strSQL & "  LABOR_TICKET" & VBCrLf
                    strSQL = strSQL & "WHERE" & VBCrLf
                    strSQL = strSQL & "  TRANSACTION_DATE>=TRUNC(SYSDATE-180)" & VBCrLf
                    strSQL = strSQL & "  AND HOURS_WORKED IS NULL" & VBCrLf
                    strSQL = strSQL & "  AND RESOURCE_ID IS NULL"
                    snpData.Open strSQL, dbMyConnection

                    If snpData.State = 1 Then
                        If Not(snpData.EOF) Then
                            strOut = "Emp on Indirect: " & cStr(snpData("num"))
                        End If
                        snpData.Close
                    End If
                Case "Non Invoiced Shipper"
                    STRSQL = "SELECT" & VBCrLf
                    strSQL = strSQL & "  S.PACKLIST_ID" & VBCrLf
                    strSQL = strSQL & "FROM" & VBCrLf
                    strSQL = strSQL & "  SHIPPER S," & VBCrLf
                    strSQL = strSQL & "  SHIPPER_LINE SL" & VBCrLf
                    strSQL = strSQL & "WHERE" & VBCrLf
                    strSQL = strSQL & "  S.INVOICE_ID IS NULL" & VBCrLf
                    strSQL = strSQL & "  AND S.STATUS='A'" & VBCrLf
                    strSQL = strSQL & "  AND S.PACKLIST_ID=SL.PACKLIST_ID" & VBCrLf
                    strSQL = strSQL & "  AND SL.SHIPPED_QTY>0" & VBCrLf
                    strSQL = strSQL & "GROUP BY" & VBCrLf
                    strSQL = strSQL & "  S.PACKLIST_ID"
                    snpData.Open strSQL, dbMyConnection

                    lngCount = 0
                    If snpData.State = 1 Then
                        Do While Not(snpData.EOF)
                            lngCount = lngCount +1

                            snpData.MoveNext
                        Loop
                        strOut = "PLs to Invoice: " & cStr(lngCount)
                        snpData.Close
                    End If
            End Select

            'Output the selections in the correct divInspector box
            Select Case i
                Case 1
                    divInspector1.InnerText = strOut
                Case 2
                    divInspector2.InnerText = strOut
                Case 3
                    divInspector3.InnerText = strOut
                Case 4
                    divInspector4.InnerText = strOut
                Case 5
                    divInspector5.InnerText = strOut
                Case 6
                    divInspector6.InnerText = strOut
                case 7
                    divInspector7.InnerText = strOut
                Case 8
                    divInspector8.InnerText = strOut
                Case 9
                    divInspector9.InnerText = strOut
                case 10
                    divInspector10.InnerText = strOut
            End Select
        Next

        divLastUpdate.InnerText = "Last Update: " & Time

        dbMyConnection.Close
    Else
        divLastUpdate.InnerText = "Could Not Connect"
    End If

    Set snpData = Nothing
    Set dbMyConnection = Nothing
End Sub

'Function SaveSettings(Event)
'    If Event.closeAction = event.Action.commit Then
'        System.Gadget.Settings.write("settingsSelectionIndex", selUserEntry.selectedIndex)
'    End If
'    'Allow the Settings dialog to close.
'    event.cancel = false
'End Function

'Function SaveSettingsClosed(event)
'    'User hits OK on the settings page.
'    If event.closeAction = event.Action.commit Then
'        userEntry = System.Gadget.Settings.readString("settingsUserEntry")
'        SetContentText userEntry
'    Else
'        If event.closeAction = event.Action.cancel Then
'            'User hits Cancel on the settings page.
'            SetContentText "Cancelled"
'        End If
'    End If
'End Function
Sub ShowBackgroundHighlight
    imgBackground.brightness = 0.0
End Sub

Sub ShowFlyOut(intInspector)
    Dim strPage

    On Error Resume Next

    'Determine which of the flyout web pages to display based on the Inspector loaded into the position
    Select Case strInpector(intInspector)
        Case "Negative On Hand"
            strPage = "FlyOutNOH.html"
        Case "Past Due PO Line Count"
            strPage = "FlyOutPOPD.html"
        Case "In Use Shop Resources"
            strPage = "FlyOutRIU.html"
        Case "Employees Clocked In"
            strPage = "FlyOutECI.html"
        Case "Employees Clocked Into Indirect"
            strPage = "FlyOutEOI.html"
        Case "Non Invoiced Shipper"
            strPage = "FlyOutPLI.html"
    End Select
    If strPage <> "" Then
        System.Gadget.Flyout.file = cStr(strPage)
        System.Gadget.Flyout.Show = true
    End If
End Sub

Private Function Switch(strValue)
    Dim i
    Dim intOffset
    Dim intTemp
    Dim strTemp
    Dim strKey

    strKey = "OracleDatabaseDoesNotNeedToBeDifficultToUnderstand"
    intOffset = 50

    strTemp = ""
    For i = 1 To Len(strValue)
        intTemp = Asc(Mid(strValue, i, 1)) Xor Asc(Mid(strKey, Len(strValue) + i, 1)) + intOffset
        If intTemp > 255 Then
            intTemp = intTemp - 255
        End If
        strTemp = strTemp & Chr(intTemp)
    Next

    Switch = strTemp
End Function

</script>

<body id="Background" onmouseover="imgBackground.brightness = 0.5" onmouseout="imgBackground.brightness = 0.0">
    <g:background id="imgBackground" src="url(Inspectorbackground.png)" brightness="0.0" style="padding: 0px"</g:background>
    <div id="divInspector1" style="top: 10px;"
          onmouseover="divInspector1.style.backgroundImage='url(InspectorButtonMouseOver.png)'"
          onmouseout="divInspector1.style.backgroundImage='url(InspectorButtonUp.png)'"
          onclick="ShowFlyOut(1)">Inspector 1</div>
    <div id="divInspector2" style="top: 25px;"
          onmouseover="divInspector2.style.backgroundImage='url(InspectorButtonMouseOver.png)'"
          onmouseout="divInspector2.style.backgroundImage='url(InspectorButtonUp.png)'"
          onclick="ShowFlyOut(2)">Inspector 2</div>
    <div id="divInspector3" style="top: 40px;"
          onmouseover="divInspector3.style.backgroundImage='url(InspectorButtonMouseOver.png)'"
          onmouseout="divInspector3.style.backgroundImage='url(InspectorButtonUp.png)'"
          onclick="ShowFlyOut(3)">Inspector 3</div>
    <div id="divInspector4" style="top: 55px;"
          onmouseover="divInspector4.style.backgroundImage='url(InspectorButtonMouseOver.png)'"
          onmouseout="divInspector4.style.backgroundImage='url(InspectorButtonUp.png)'"
          onclick="ShowFlyOut(4)">Inspector 4</div>
    <div id="divInspector5" style="top: 70px;"
          onmouseover="divInspector5.style.backgroundImage='url(InspectorButtonMouseOver.png)'"
          onmouseout="divInspector5.style.backgroundImage='url(InspectorButtonUp.png)'"
          onclick="ShowFlyOut(5)">Inspector 5</div>
    <div id="divInspector6" style="top: 85px;"
          onmouseover="divInspector6.style.backgroundImage='url(InspectorButtonMouseOver.png)'"
          onmouseout="divInspector6.style.backgroundImage='url(InspectorButtonUp.png)'"
          onclick="ShowFlyOut(6)">Inspector 6</div>
    <div id="divInspector7" style="top: 100px;"
          onmouseover="divInspector7.style.backgroundImage='url(InspectorButtonMouseOver.png)'"
          onmouseout="divInspector7.style.backgroundImage='url(InspectorButtonUp.png)'"
          onclick="ShowFlyOut(7)">Inspector 7</div>
    <div id="divInspector8" style="top: 115px;"
          onmouseover="divInspector8.style.backgroundImage='url(InspectorButtonMouseOver.png)'"
          onmouseout="divInspector8.style.backgroundImage='url(InspectorButtonUp.png)'"
          onclick="ShowFlyOut(8)">Inspector 8</div>
    <div id="divInspector9" style="top: 130px;"
          onmouseover="divInspector9.style.backgroundImage='url(InspectorButtonMouseOver.png)'"
          onmouseout="divInspector9.style.backgroundImage='url(InspectorButtonUp.png)'"
          onclick="ShowFlyOut(9)">Inspector 9</div>
    <div id="divInspector10" style="top: 145px;"
          onmouseover="divInspector10.style.backgroundImage='url(InspectorButtonMouseOver.png)'"
          onmouseout="divInspector10.style.backgroundImage='url(InspectorButtonUp.png)'"
          onclick="ShowFlyOut(10)">Inspector 10</div>

    <div id="divLastUpdate" style="background-position: center center; padding: 0px; font-family: Perpetua; font-size: 10px; font-weight: bold; position: absolute; top: 169px; left: 12px; color: #000000;">Time</div>  
    <div id="divTitle"
        style="background-position: left center; border-style: none; border-width: 0px; padding: 0px; margin: 0px; position: absolute; top: 178px; left: 3px; height: 25px; width: 135px; background-image: url(Inspector.png); background-repeat: no-repeat;">
        </div>
    <div id="div1"
        style="background-position: left center; border-style: none; border-width: 0px; padding: 0px; margin: 0px; position: absolute; top: 200px; left: 3px; height: 25px; width: 135px; background-image: url(Inspector.png); background-repeat: no-repeat; visibility: hidden;">
        </div>
</body>
</html>

 FlyOutECI.html:

<html>
<head>
    <title>K&M Inspector</title>
    <style type="text/css">
        body
        {
            margin: 0px;
            width: 655px;
            height: 300px;
        }
        <!--table { font-size: 8pt; font-family: Times New Roman } -->
    </style>
</head>

<script language="VBScript">
Option Explicit

Sub Window_Onload
    Dim dbMyConnection
    Dim comData
    Dim snpData
    Dim strUsername
    Dim strPassword
    Dim strDatabase
    Dim strSQL
    Dim strHTML

    On Error Resume Next

    Set snpData = CreateObject("ADODB.Recordset")
    Set dbMyConnection = CreateObject("ADODB.Connection")

    strUsername = "MyUser"
    strPassword = Switch(Chr(112) & Chr(100) & Chr(15) & Chr(10) & Chr(180) & Chr(7) & Chr(206) & Chr(233) & Chr(25))
    strDatabase = "MyDB"

    dbMyConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
    dbMyConnection.Open

    If dbMyConnection.State = 1 Then
        strSQL = "SELECT" & VBCrLf
        strSQL = strSQL & "  LT.EMPLOYEE_ID," & VBCrLf
        strSQL = strSQL & "  E.LAST_NAME || ', ' || E.FIRST_NAME AS EMPLOYEE_NAME," & VBCrLf
        strSQL = strSQL & "  LT.RESOURCE_ID," & VBCrLf
        strSQL = strSQL & "  LT.WORKORDER_BASE_ID," & VBCrLf
        strSQL = strSQL & "  LT.WORKORDER_LOT_ID," & VBCrLf
        strSQL = strSQL & "  LT.WORKORDER_SPLIT_ID," & VBCrLf
        strSQL = strSQL & "  LT.WORKORDER_SUB_ID," & VBCrLf
        strSQL = strSQL & "  LT.OPERATION_SEQ_NO," & VBCrLf
        strSQL = strSQL & "  WO.PART_ID," & VBCrLf
        strSQL = strSQL & "  LT.INDIRECT_ID," & VBCrLf
        strSQL = strSQL & "  NVL(LT.ACT_CLOCK_IN,LT.CLOCK_IN) AS CLOCK_IN" & VBCrLf
        strSQL = strSQL & "FROM" & VBCrLf
        strSQL = strSQL & "  LABOR_TICKET LT," & VBCrLf
        strSQL = strSQL & "  EMPLOYEE E," & VBCrLf
        strSQL = strSQL & "  WORK_ORDER WO" & VBCrLf
        strSQL = strSQL & "WHERE" & VBCrLf
        strSQL = strSQL & "  LT.TRANSACTION_DATE>=TRUNC(SYSDATE-180)" & VBCrLf
        strSQL = strSQL & "  AND LT.HOURS_WORKED IS NULL" & VBCrLf
        strSQL = strSQL & "  AND LT.EMPLOYEE_ID=E.ID" & VBCrLf
        strSQL = strSQL & "  AND LT.WORKORDER_TYPE=WO.TYPE(+)" & VBCrLf
        strSQL = strSQL & "  AND LT.WORKORDER_BASE_ID=WO.BASE_ID(+)" & VBCrLf
        strSQL = strSQL & "  AND LT.WORKORDER_LOT_ID=WO.LOT_ID(+)" & VBCrLf
        strSQL = strSQL & "  AND LT.WORKORDER_SPLIT_ID=WO.SPLIT_ID(+)" & VBCrLf
        strSQL = strSQL & "  AND WO.SUB_ID(+)='0'" & VBCrLf
        strSQL = strSQL & "ORDER BY" & VBCrLf
        strSQL = strSQL & "  E.LAST_NAME || ', ' || E.FIRST_NAME," & VBCrLf
        strSQL = strSQL & "  NVL(LT.ACT_CLOCK_IN,LT.CLOCK_IN)"

        snpData.Open strSQL, dbMyConnection

        If snpData.State = 1 Then
            strHTML = strHTML & "<b>Employees Clocked In</b>" & vbCrLf
            strHTML = strHTML & "<table width=640 border=1>" & vbCrLf
            strHTML = strHTML & "<tr bgcolor=#5555FF>"
            strHTML = strHTML & "<td><b>Emp ID</b></td>"
            strHTML = strHTML & "<td><b>Name</b></td>"
            strHTML = strHTML & "<td><b>Resource ID</b></td>"
            strHTML = strHTML & "<td><b>Work Order Op</b></td>"
            strHTML = strHTML & "<td><b>Part ID</b></td>"
            strHTML = strHTML & "<td><b>Clock In</b></td>"
            strHTML = strHTML & "</tr>" & vbCrLf

            Do While Not snpData.EOF
                strHTML = strHTML & "<tr>"
                strHTML = strHTML & "<td>" & cStr(snpData("employee_id")) & "</td>"
                If Not IsNull(snpData("employee_name")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("employee_name")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("resource_id")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("resource_id")) & "</td>"
                    If cStr(snpData("workorder_sub_id")) = "0" Then
                        strHTML = strHTML & "<td>" & cStr(snpData("workorder_base_id")) & "/" & cStr(snpData("workorder_lot_id")) & "/ OP " & cStr(snpData("operation_seq_no")) & "</td>"
                    Else
                        strHTML = strHTML & "<td>" & cStr(snpData("workorder_base_id")) & "-" & cStr(snpData("workorder_sub_id")) & "/" & cStr(snpData("workorder_lot_id")) & "/ OP " & cStr(snpData("operation_seq_no")) & "</td>"
                    End If
                    If Not IsNull(snpData("part_id")) Then
                        strHTML = strHTML & "<td>" & cStr(snpData("part_id")) & "</td>"
                    Else
                        strHTML = strHTML & "<td>&nbsp;</td>"
                    End If
                Else
                    strHTML = strHTML & "<td>Indirect: " & cStr(snpData("indirect_id")) & "</td>"
                    strHTML = strHTML & "<td>&nbsp;</td>"
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("clock_in")) Then
                    strHTML = strHTML & "<td><p align=""right"">" & cStr(snpData("clock_in")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                strHTML = strHTML & "</tr>" & vbCrLf

                snpData.MoveNext
            Loop

            strHTML = strHTML & "</table>" & vbCrLf
            snpData.Close

            divOutput.InnerHTML = strHTML
        Else
            divOutput.InnerText = "No Rows Returned"
        End If

        dbMyConnection.Close
    Else
        'Could Not Connect
        divOutput.InnerText = "Could Not Connect"
    End If

    Set snpData = Nothing
    Set dbMyConnection = Nothing
End Sub

Private Function Switch(strValue)
    Dim i
    Dim intOffset
    Dim intTemp
    Dim strTemp
    Dim strKey

    strKey = "OracleDatabaseDoesNotNeedToBeDifficultToUnderstand"
    intOffset = 50

    strTemp = ""
    For i = 1 To Len(strValue)
        intTemp = Asc(Mid(strValue, i, 1)) Xor Asc(Mid(strKey, Len(strValue) + i, 1)) + intOffset
        If intTemp > 255 Then
            intTemp = intTemp - 255
        End If
        strTemp = strTemp & Chr(intTemp)
    Next

    Switch = strTemp
End Function

</script>

<body id="Background">
    <div id="divOutput" style="position: absolute; top: 10px; width: 645px; height: 280px; overflow-y:auto;">&nbsp;</div> 
</body>
</html>

 —

FlyOutEOI.html:

<html>
<head>
    <title>K&M Inspector</title>
    <style type="text/css">
        body
        {
            margin: 0px;
            width: 505px;
            height: 300px;
        }
        <!--table { font-size: 8pt; font-family: Times New Roman } -->
    </style>
</head>

<script language="VBScript">
Option Explicit

Sub Window_Onload
    Dim dbMyConnection
    Dim comData
    Dim snpData
    Dim strUsername
    Dim strPassword
    Dim strDatabase
    Dim strSQL
    Dim strHTML

    On Error Resume Next

    Set snpData = CreateObject("ADODB.Recordset")
    Set dbMyConnection = CreateObject("ADODB.Connection")

    strUsername = "MyUser"
    strPassword = Switch(Chr(112) & Chr(100) & Chr(15) & Chr(10) & Chr(180) & Chr(7) & Chr(206) & Chr(233) & Chr(25))
    strDatabase = "MyDB"

    dbMyConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
    dbMyConnection.Open

    If dbMyConnection.State = 1 Then
        strSQL = "SELECT" & VBCrLf
        strSQL = strSQL & "  LT.EMPLOYEE_ID," & VBCrLf
        strSQL = strSQL & "  E.LAST_NAME || ', ' || E.FIRST_NAME AS EMPLOYEE_NAME," & VBCrLf
        strSQL = strSQL & "  LT.INDIRECT_ID," & VBCrLf
        strSQL = strSQL & "  I.DESCRIPTION," & VBCrLf
        strSQL = strSQL & "  NVL(LT.ACT_CLOCK_IN,LT.CLOCK_IN) AS CLOCK_IN" & VBCrLf
        strSQL = strSQL & "FROM" & VBCrLf
        strSQL = strSQL & "  LABOR_TICKET LT," & VBCrLf
        strSQL = strSQL & "  EMPLOYEE E," & VBCrLf
        strSQL = strSQL & "  INDIRECT I" & VBCrLf
        strSQL = strSQL & "WHERE" & VBCrLf
        strSQL = strSQL & "  LT.TRANSACTION_DATE>=TRUNC(SYSDATE-180)" & VBCrLf
        strSQL = strSQL & "  AND LT.HOURS_WORKED IS NULL" & VBCrLf
        strSQL = strSQL & "  AND LT.EMPLOYEE_ID=E.ID" & VBCrLf
        strSQL = strSQL & "  AND LT.INDIRECT_ID IS NOT NULL" & VBCrLf
        strSQL = strSQL & "  AND LT.INDIRECT_ID=I.ID" & VBCrLf
        strSQL = strSQL & "ORDER BY" & VBCrLf
        strSQL = strSQL & "  E.LAST_NAME || ', ' || E.FIRST_NAME," & VBCrLf
        strSQL = strSQL & "  NVL(LT.ACT_CLOCK_IN,LT.CLOCK_IN)"

        snpData.Open strSQL, dbMyConnection

        If snpData.State = 1 Then
            strHTML = strHTML & "<b>Employees Clocked Into Indirect</b>" & vbCrLf
            strHTML = strHTML & "<table width=490 border=1>" & vbCrLf
            strHTML = strHTML & "<tr bgcolor=#5555FF>"
            strHTML = strHTML & "<td><b>Emp ID</b></td>"
            strHTML = strHTML & "<td><b>Name</b></td>"
            strHTML = strHTML & "<td><b>Indirect</b></td>"
            strHTML = strHTML & "<td><b>Description</b></td>"
            strHTML = strHTML & "<td><b>Clock In</b></td>"
            strHTML = strHTML & "</tr>" & vbCrLf

            Do While Not snpData.EOF
                strHTML = strHTML & "<tr>"
                strHTML = strHTML & "<td>" & cStr(snpData("employee_id")) & "</td>"
                If Not IsNull(snpData("employee_name")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("employee_name")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                strHTML = strHTML &  "<td>" & cStr(snpData("indirect_id")) & "</td>"
                If Not IsNull(snpData("description")) Then
                    strHTML = strHTML &  "<td>" & cStr(snpData("description")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("clock_in")) Then
                    strHTML = strHTML & "<td><p align=""right"">" & cStr(snpData("clock_in")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                strHTML = strHTML & "</tr>" & vbCrLf

                snpData.MoveNext
            Loop

            strHTML = strHTML & "</table>" & vbCrLf
            snpData.Close

            divOutput.InnerHTML = strHTML
        Else
            divOutput.InnerText = "No Rows Returned"
        End If

        dbMyConnection.Close
    Else
        'Could Not Connect
        divOutput.InnerText = "Could Not Connect"
    End If

    Set snpData = Nothing
    Set dbMyConnection = Nothing
End Sub

Private Function Switch(strValue)
    Dim i
    Dim intOffset
    Dim intTemp
    Dim strTemp
    Dim strKey

    strKey = "OracleDatabaseDoesNotNeedToBeDifficultToUnderstand"
    intOffset = 50

    strTemp = ""
    For i = 1 To Len(strValue)
        intTemp = Asc(Mid(strValue, i, 1)) Xor Asc(Mid(strKey, Len(strValue) + i, 1)) + intOffset
        If intTemp > 255 Then
            intTemp = intTemp - 255
        End If
        strTemp = strTemp & Chr(intTemp)
    Next

    Switch = strTemp
End Function
</script>

<body id="Background">
    <div id="divOutput" style="position: absolute; top: 10px; width: 495px; height: 280px; overflow-y:auto;">&nbsp;</div> 
</body>
</html>

FlyOutNOH.html:

<html>
<head>
    <title>K&M Inspector</title>
    <style type="text/css">
        body
        {
            margin: 0px;
            width: 505px;
            height: 300px;
        }
        <!--table { font-size: 8pt; font-family: Times New Roman } -->
    </style>
</head>

<script language="VBScript">
Option Explicit

Sub Window_Onload
    Dim dbMyConnection
    Dim comData
    Dim snpData
    Dim strUsername
    Dim strPassword
    Dim strDatabase
    Dim strSQL
    Dim strHTML

    On Error Resume Next

    Set snpData = CreateObject("ADODB.Recordset")
    Set dbMyConnection = CreateObject("ADODB.Connection")

    strUsername = "MyUser"
    strPassword = Switch(Chr(112) & Chr(100) & Chr(15) & Chr(10) & Chr(180) & Chr(7) & Chr(206) & Chr(233) & Chr(25))
    strDatabase = "MyDB"

    dbMyConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
    dbMyConnection.Open

    If dbMyConnection.State = 1 Then
        strSQL = "SELECT" & VBCrLf
        strSQL = strSQL & "  ID," & VBCrLf
        strSQL = strSQL & "  DESCRIPTION," & VBCrLf
        strSQL = strSQL & "  PRODUCT_CODE," & VBCrLf
        strSQL = strSQL & "  COMMODITY_CODE," & VBCrLf
        strSQL = strSQL & "  QTY_ON_HAND," & VBCrLf
        strSQL = strSQL & "  DECODE(PURCHASED,'Y','Purchased','Fabricated') PART_TYPE" & VBCrLf
        strSQL = strSQL & "FROM" & VBCrLf
        strSQL = strSQL & "  PART" & VBCrLf
        strSQL = strSQL & "WHERE" & VBCrLf
        strSQL = strSQL & "  QTY_ON_HAND<0" & VBCrLf
        strSQL = strSQL & "ORDER BY" & VBCrLf
        strSQL = strSQL & "  ID"
        snpData.Open strSQL, dbMyConnection

        If snpData.State = 1 Then
            strHTML = strHTML & "<b>Parts with a Negative QTY On Hand</b>" & vbCrLf
            strHTML = strHTML & "<table width=490 border=1>" & vbCrLf
            strHTML = strHTML & "<tr bgcolor=#5555FF>"
            strHTML = strHTML & "<td><b>Part ID</b></td>"
            strHTML = strHTML & "<td><b>Description</b></td>"
            strHTML = strHTML & "<td><b>P.C.</b></td>"
            strHTML = strHTML & "<td><b>C.C.</b></td>"
            strHTML = strHTML & "<td><b>Qty</b></td>"
            strHTML = strHTML & "<td><b>Type</b></td>"
            strHTML = strHTML & "</tr>" & vbCrLf

            Do While Not snpData.EOF
                strHTML = strHTML & "<tr>"
                strHTML = strHTML & "<td>" & cStr(snpData("id")) & "</td>"
                If Not IsNull(snpData("description")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("description")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("product_code")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("product_code")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("commodity_code")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("commodity_code")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("qty_on_hand")) Then
                    strHTML = strHTML & "<td><p align=""right"">" & FormatNumber(snpData("qty_on_hand"),4) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("part_type")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("part_type")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                strHTML = strHTML & "</tr>" & vbCrLf

                snpData.MoveNext
            Loop

            strHTML = strHTML & "</table>" & vbCrLf
            snpData.Close

            divOutput.InnerHTML = strHTML
        Else
            divOutput.InnerText = "No Rows Returned"
        End If

        dbMyConnection.Close
    Else
        'Could Not Connect
        divOutput.InnerText = "Could Not Connect"
    End If

    Set snpData = Nothing
    Set dbMyConnection = Nothing
End Sub

Private Function Switch(strValue)
    Dim i
    Dim intOffset
    Dim intTemp
    Dim strTemp
    Dim strKey

    strKey = "OracleDatabaseDoesNotNeedToBeDifficultToUnderstand"
    intOffset = 50

    strTemp = ""
    For i = 1 To Len(strValue)
        intTemp = Asc(Mid(strValue, i, 1)) Xor Asc(Mid(strKey, Len(strValue) + i, 1)) + intOffset
        If intTemp > 255 Then
            intTemp = intTemp - 255
        End If
        strTemp = strTemp & Chr(intTemp)
    Next

    Switch = strTemp
End Function

</script>

<body id="Background">
    <div id="divOutput" style="position: absolute; top: 10px; width: 495px; height: 280px; overflow-y:auto;">&nbsp;</div> 
</body>
</html>

FlyOutPLI.html:

 <html>
<head>
    <title>K&M Inspector</title>
    <style type="text/css">
        body
        {
            margin: 0px;
            width: 655px;
            height: 300px;
        }
        <!--table { font-size: 8pt; font-family: Times New Roman } -->
    </style>
</head>

<script language="VBScript">
Option Explicit

Sub Window_Onload
    Dim dbMyConnection
    Dim comData
    Dim snpData
    Dim strUsername
    Dim strPassword
    Dim strDatabase
    Dim strLastPacklist
    Dim strSQL
    Dim strHTML

    On Error Resume Next

    Set snpData = CreateObject("ADODB.Recordset")
    Set dbMyConnection = CreateObject("ADODB.Connection")

    strUsername = "MyUser"
    strPassword = Switch(Chr(112) & Chr(100) & Chr(15) & Chr(10) & Chr(180) & Chr(7) & Chr(206) & Chr(233) & Chr(25))
    strDatabase = "MyDB"

    dbMyConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
    dbMyConnection.Open

    If dbMyConnection.State = 1 Then
        strSQL = "SELECT" & VBCrLf
        strSQL = strSQL & "  S.PACKLIST_ID," & VBCrLf
        strSQL = strSQL & "  SL.LINE_NO," & VBCrLf
        strSQL = strSQL & "  S.BOL_ID," & VBCrLf
        strSQL = strSQL & "  S.SHIPPED_DATE," & VBCrLf
        strSQL = strSQL & "  CO.CUSTOMER_ID," & VBCrLf
        strSQL = strSQL & "  SL.CUST_ORDER_ID," & VBCrLf
        strSQL = strSQL & "  SL.CUST_ORDER_LINE_NO," & VBCrLf
        strSQL = strSQL & "  COL.PART_ID," & VBCrLf
        strSQL = strSQL & "  SL.SHIPPED_QTY" & VBCrLf
        strSQL = strSQL & "FROM" & VBCrLf
        strSQL = strSQL & "  SHIPPER S," & VBCrLf
        strSQL = strSQL & "  SHIPPER_LINE SL," & VBCrLf
        strSQL = strSQL & "  CUST_ORDER_LINE COL," & VBCrLf
        strSQL = strSQL & "  CUSTOMER_ORDER CO" & VBCrLf
        strSQL = strSQL & "WHERE" & VBCrLf
        strSQL = strSQL & "  S.INVOICE_ID IS NULL" & VBCrLf
        strSQL = strSQL & "  AND S.STATUS='A'" & VBCrLf
        strSQL = strSQL & "  AND S.PACKLIST_ID=SL.PACKLIST_ID" & VBCrLf
        strSQL = strSQL & "  AND SL.CUST_ORDER_ID=COL.CUST_ORDER_ID(+)" & VBCrLf
        strSQL = strSQL & "  AND SL.CUST_ORDER_LINE_NO=COL.LINE_NO(+)" & VBCrLf
        strSQL = strSQL & "  AND COL.CUST_ORDER_ID=CO.ID(+)" & VBCrLf
        strSQL = strSQL & "ORDER BY" & VBCrLf
        strSQL = strSQL & "  S.PACKLIST_ID," & VBCrLf
        strSQL = strSQL & "  SL.LINE_NO"

        snpData.Open strSQL, dbMyConnection

        If snpData.State = 1 Then
            strHTML = strHTML & "<b>Pack Lists Not Yet Invoiced</b>" & vbCrLf
            strHTML = strHTML & "<table width=640 border=1>" & vbCrLf
            strHTML = strHTML & "<tr bgcolor=#5555FF>"
            strHTML = strHTML & "<td><b>PL</b></td>"
            strHTML = strHTML & "<td><b>Line</b></td>"
            strHTML = strHTML & "<td><b>BOL</b></td>"
            strHTML = strHTML & "<td><b>Ship Date</b></td>"
            strHTML = strHTML & "<td><b>Customer</b></td>"
            strHTML = strHTML & "<td><b>Cust Order</b></td>"
            strHTML = strHTML & "<td><b>Part ID</b></td>"
            strHTML = strHTML & "<td><b>Qty</b></td>"
            strHTML = strHTML & "</tr>" & vbCrLf

            Do While Not snpData.EOF
                strHTML = strHTML & "<tr>"
                If strLastPacklist <> snpData("packlist_id") Then
                    'Need to output the pack list ID this time, since it is not the same as the last row returned by the database
                    strHTML = strHTML & "<td>" & cStr(snpData("packlist_id")) & "</td>"   
                    strLastPacklist = snpData("packlist_id")
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                strHTML = strHTML & "<td>" & cStr(snpData("line_no")) & "</td>"
                If Not IsNull(snpData("bol_id")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("bol_id")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("shipped_date")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("shipped_date")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("customer_id")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("customer_id")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("cust_order_id")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("cust_order_id")) & "/" & cStr(snpData("cust_order_line_no")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("part_id")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("part_id")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("shipped_qty")) Then
                    strHTML = strHTML & "<td><p align=""right"">" & cStr(snpData("shipped_qty")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                strHTML = strHTML & "</tr>" & vbCrLf

                snpData.MoveNext
            Loop

            strHTML = strHTML & "</table>" & vbCrLf
            snpData.Close

            divOutput.InnerHTML = strHTML
        Else
            divOutput.InnerText = "No Rows Returned"
        End If

        dbMyConnection.Close
    Else
        'Could Not Connect
        divOutput.InnerText = "Could Not Connect"
    End If

    Set snpData = Nothing
    Set dbMyConnection = Nothing
End Sub

Private Function Switch(strValue)
    Dim i
    Dim intOffset
    Dim intTemp
    Dim strTemp
    Dim strKey

    strKey = "OracleDatabaseDoesNotNeedToBeDifficultToUnderstand"
    intOffset = 50

    strTemp = ""
    For i = 1 To Len(strValue)
        intTemp = Asc(Mid(strValue, i, 1)) Xor Asc(Mid(strKey, Len(strValue) + i, 1)) + intOffset
        If intTemp > 255 Then
            intTemp = intTemp - 255
        End If
        strTemp = strTemp & Chr(intTemp)
    Next

    Switch = strTemp
End Function

</script>

<body id="Background">
    <div id="divOutput" style="position: absolute; top: 10px; width: 645px; height: 280px; overflow-y:auto;">&nbsp;</div> 
</body>
</html>

FlyOutPOPD.html:

<html>
<head>
    <title>K&M Inspector</title>
    <style type="text/css">
        body
        {
            margin: 0px;
            width: 610px;
            height: 500px;
        }
        <!--table { font-size: 8pt; font-family: Times New Roman } -->
    </style>
</head>

<script language="VBScript">
Option Explicit

Sub Window_Onload
    Dim dbMyConnection
    Dim comData
    Dim snpData
    Dim strUsername
    Dim strPassword
    Dim strDatabase
    Dim strSQL
    Dim strHTML

    'On Error Resume Next

    Set snpData = CreateObject("ADODB.Recordset")
    Set dbMyConnection = CreateObject("ADODB.Connection")

    strUsername = "MyUser"
    strPassword = Switch(Chr(112) & Chr(100) & Chr(15) & Chr(10) & Chr(180) & Chr(7) & Chr(206) & Chr(233) & Chr(25))
    strDatabase = "MyDB"

    dbMyConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
    dbMyConnection.Open

    If dbMyConnection.State = 1 Then
        strSQL = "SELECT" & VBCrLf
        strSQL = strSQL & "  POL.PART_ID," & VBCrLf
        strSQL = strSQL & "  POL.VENDOR_PART_ID," & VBCrLf
        strSQL = strSQL & "  P.DESCRIPTION," & VBCrLf
        strSQL = strSQL & "  POL.PURC_ORDER_ID," & VBCrLf
        strSQL = strSQL & "  POL.LINE_NO AS PURC_ORDER_LINE_NO," & VBCrLf
        strSQL = strSQL & "  PLD.DEL_SCHED_LINE_NO," & VBCrLf
        strSQL = strSQL & "  COALESCE(PLD.DESIRED_RECV_DATE,POL.DESIRED_RECV_DATE,PO.DESIRED_RECV_DATE) AS DESIRED_RECV_DATE," & VBCrLf
        strSQL = strSQL & "  DECODE(PLD.PURC_ORDER_LINE_NO,NULL,NVL(POL.ORDER_QTY,0),NVL(PLD.ORDER_QTY,0)) AS ORDER_QTY," & VBCrLf
        strSQL = strSQL & "  DECODE(PLD.PURC_ORDER_LINE_NO,NULL,NVL(TOTAL_RECEIVED_QTY,0),NVL(PLD.RECEIVED_QTY,0)) AS RECEIVED_QTY" & VBCrLf
        strSQL = strSQL & "FROM" & VBCrLf
        strSQL = strSQL & "  PURCHASE_ORDER PO," & VBCrLf
        strSQL = strSQL & "  PURC_ORDER_LINE POL," & VBCrLf
        strSQL = strSQL & "  PART P," & VBCrLf
        strSQL = strSQL & "  PURC_LINE_DEL PLD" & VBCrLf
        strSQL = strSQL & "WHERE" & VBCrLf
        strSQL = strSQL & "  PO.STATUS IN ('F','R','U')" & VBCrLf
        strSQL = strSQL & "  AND PO.ID=POL.PURC_ORDER_ID" & VBCrLf
        strSQL = strSQL & "  AND POL.LINE_STATUS='A'" & VBCrLf
        strSQL = strSQL & "  AND POL.SERVICE_ID IS NULL" & VBCrLf
        strSQL = strSQL & "  AND POL.ORDER_QTY>POL.TOTAL_RECEIVED_QTY" & VBCrLf
        strSQL = strSQL & "  AND POL.PART_ID=P.ID(+)" & VBCrLf
        strSQL = strSQL & "  AND POL.PURC_ORDER_ID=PLD.PURC_ORDER_ID(+)" & VBCrLf
        strSQL = strSQL & "  AND POL.LINE_NO=PLD.PURC_ORDER_LINE_NO(+)" & VBCrLf
        strSQL = strSQL & "  AND DECODE(PLD.PURC_ORDER_LINE_NO,NULL,NVL(POL.ORDER_QTY,0),NVL(PLD.ORDER_QTY,0)) > DECODE(PLD.PURC_ORDER_LINE_NO,NULL,NVL(TOTAL_RECEIVED_QTY,0),NVL(PLD.RECEIVED_QTY,0))" & VBCrLf
        strSQL = strSQL & "  AND COALESCE(PLD.DESIRED_RECV_DATE,POL.DESIRED_RECV_DATE,PO.DESIRED_RECV_DATE) < SYSDATE" & VBCrLf
        strSQL = strSQL & "ORDER BY" & VBCrLf
        strSQL = strSQL & "  POL.PART_ID," & vbCrLf
        strSQL = strSQL & "  POL.PURC_ORDER_ID," & VBCrLf
        strSQL = strSQL & "  POL.LINE_NO," & VBCrLf
        strSQL = strSQL & "  PLD.DEL_SCHED_LINE_NO" & VBCrLf
        snpData.Open strSQL, dbMyConnection

        If snpData.State = 1 Then
            strHTML = strHTML & "<b>Past Due Purchase Orders</b>" & vbCrLf
            strHTML = strHTML & "<table width=590 border=1>" & vbCrLf
            strHTML = strHTML & "<tr bgcolor=#5555FF>"
            strHTML = strHTML & "<td><b>Part ID</b></td>"
            strHTML = strHTML & "<td><b>Description</b></td>"
            strHTML = strHTML & "<td><b>PO</b></td>"
            strHTML = strHTML & "<td><b>Received</b></td>"
            strHTML = strHTML & "<td><b>Wanted</b></td>"
            strHTML = strHTML & "</tr>" & vbCrLf

            Do While Not snpData.EOF
                strHTML = strHTML & "<tr>"
                If Not IsNull(snpData("part_id")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("part_id")) & "</td>"
                Else
                    If Not IsNull(snpData("vendor_part_id")) Then
                        strHTML = strHTML & "<td>" & cStr(snpData("vendor_part_id")) & "</td>"
                    Else
                        strHTML = strHTML & "<td>&nbsp;</td>"
                    End If
                End If
                If Not IsNull(snpData("description")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("description")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("del_sched_line_no")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("purc_order_id")) & "/" & cStr(snpData("purc_order_line_no")) & "/DL " & cStr(snpData("del_sched_line_no")) & "</td>"
                Else
                    strHTML = strHTML & "<td>" & cStr(snpData("purc_order_id")) & "/" & cStr(snpData("purc_order_line_no")) & "</td>"
                End If
                strHTML = strHTML & "<td>" & cStr(snpData("received_qty")) & " of " & cStr(snpData("order_qty")) & "</td>"
                If Not IsNull(snpData("desired_recv_date")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("desired_recv_date")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                strHTML = strHTML & "</tr>" & vbCrLf

                snpData.MoveNext
            Loop

            strHTML = strHTML & "</table>" & vbCrLf
            snpData.Close

            divOutput.InnerHTML = strHTML
        Else
            divOutput.InnerText = "No Rows Returned"
        End If

        dbMyConnection.Close
    Else
        'Could Not Connect
        divOutput.InnerText = "Could Not Connect"
    End If

    Set snpData = Nothing
    Set dbMyConnection = Nothing
End Sub

Private Function Switch(strValue)
    Dim i
    Dim intOffset
    Dim intTemp
    Dim strTemp
    Dim strKey

    strKey = "OracleDatabaseDoesNotNeedToBeDifficultToUnderstand"
    intOffset = 50

    strTemp = ""
    For i = 1 To Len(strValue)
        intTemp = Asc(Mid(strValue, i, 1)) Xor Asc(Mid(strKey, Len(strValue) + i, 1)) + intOffset
        If intTemp > 255 Then
            intTemp = intTemp - 255
        End If
        strTemp = strTemp & Chr(intTemp)
    Next

    Switch = strTemp
End Function

</script>

<body id="Background">
    <div id="divOutput" style="position: absolute; top: 10px; width: 590px; height: 480px; overflow-y:auto;">&nbsp;</div> 
</body>
</html>

FlyOutRIU.html:

<html>
<head>
    <title>K&M Inspector</title>
    <style type="text/css">
        body
        {
            margin: 0px;
            width: 655px;
            height: 300px;
        }
        <!--table { font-size: 8pt; font-family: Times New Roman } -->
    </style>
</head>

<script language="VBScript">
Option Explicit

Sub Window_Onload
    Dim dbMyConnection
    Dim comData
    Dim snpData
    Dim strUsername
    Dim strPassword
    Dim strDatabase
    Dim strSQL
    Dim strHTML

    On Error Resume Next

    Set snpData = CreateObject("ADODB.Recordset")
    Set dbMyConnection = CreateObject("ADODB.Connection")

    strUsername = "MyUser"
    strPassword = Switch(Chr(112) & Chr(100) & Chr(15) & Chr(10) & Chr(180) & Chr(7) & Chr(206) & Chr(233) & Chr(25))
    strDatabase = "MyDB"

    dbMyConnection.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";"
    dbMyConnection.Open

    If dbMyConnection.State = 1 Then
        strSQL = "SELECT" & VBCrLf
        strSQL = strSQL & "  LT.RESOURCE_ID," & VBCrLf
        strSQL = strSQL & "  SR.DESCRIPTION," & VBCrLf
        strSQL = strSQL & "  LT.WORKORDER_BASE_ID," & VBCrLf
        strSQL = strSQL & "  LT.WORKORDER_LOT_ID," & VBCrLf
        strSQL = strSQL & "  LT.WORKORDER_SPLIT_ID," & VBCrLf
        strSQL = strSQL & "  LT.WORKORDER_SUB_ID," & VBCrLf
        strSQL = strSQL & "  LT.OPERATION_SEQ_NO," & VBCrLf
        strSQL = strSQL & "  WO.PART_ID," & VBCrLf
        strSQL = strSQL & "  NVL(LT.ACT_CLOCK_IN,LT.CLOCK_IN) AS CLOCK_IN" & VBCrLf
        strSQL = strSQL & "FROM" & VBCrLf
        strSQL = strSQL & "  LABOR_TICKET LT," & VBCrLf
        strSQL = strSQL & "  SHOP_RESOURCE SR," & VBCrLf
        strSQL = strSQL & "  WORK_ORDER WO" & VBCrLf
        strSQL = strSQL & "WHERE" & VBCrLf
        strSQL = strSQL & "  LT.TRANSACTION_DATE>=TRUNC(SYSDATE-180)" & VBCrLf
        strSQL = strSQL & "  AND LT.HOURS_WORKED IS NULL" & VBCrLf
        strSQL = strSQL & "  AND LT.RESOURCE_ID IS NOT NULL" & VBCrLf
        strSQL = strSQL & "  AND LT.RESOURCE_ID=SR.ID" & VBCrLf
        strSQL = strSQL & "  AND LT.WORKORDER_TYPE='W'" & VBCrLf
        strSQL = strSQL & "  AND WO.TYPE='W'" & VBCrLf
        strSQL = strSQL & "  AND LT.WORKORDER_TYPE=WO.TYPE" & VBCrLf
        strSQL = strSQL & "  AND LT.WORKORDER_BASE_ID=WO.BASE_ID" & VBCrLf
        strSQL = strSQL & "  AND LT.WORKORDER_LOT_ID=WO.LOT_ID" & VBCrLf
        strSQL = strSQL & "  AND LT.WORKORDER_SPLIT_ID=WO.SPLIT_ID" & VBCrLf
        strSQL = strSQL & "  AND WO.SUB_ID='0'" & VBCrLf
        strSQL = strSQL & "ORDER BY" & VBCrLf
        strSQL = strSQL & "  LT.RESOURCE_ID," & VBCrLf
        strSQL = strSQL & "  NVL(LT.ACT_CLOCK_IN,LT.CLOCK_IN)" & VBCrLf

        snpData.Open strSQL, dbMyConnection

        If snpData.State = 1 Then
            strHTML = strHTML & "<b>Shop Resources Currently in Use</b>" & vbCrLf
            strHTML = strHTML & "<table width=640 border=1>" & vbCrLf
            strHTML = strHTML & "<tr bgcolor=#5555FF>"
            strHTML = strHTML & "<td><b>Resource ID</b></td>"
            strHTML = strHTML & "<td><b>Description</b></td>"
            strHTML = strHTML & "<td><b>Work Order Op</b></td>"
            strHTML = strHTML & "<td><b>Part ID</b></td>"
            strHTML = strHTML & "<td><b>Clock In</b></td>"
            strHTML = strHTML & "</tr>" & vbCrLf

            Do While Not snpData.EOF
                strHTML = strHTML & "<tr>"
                strHTML = strHTML & "<td>" & cStr(snpData("resource_id")) & "</td>"
                If Not IsNull(snpData("description")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("description")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If cStr(snpData("workorder_sub_id")) = "0" Then
                    strHTML = strHTML & "<td>" & cStr(snpData("workorder_base_id")) & "/" & cStr(snpData("workorder_lot_id")) & "/ OP " & cStr(snpData("operation_seq_no")) & "</td>"
                Else
                    strHTML = strHTML & "<td>" & cStr(snpData("workorder_base_id")) & "-" & cStr(snpData("workorder_sub_id")) & "/" & cStr(snpData("workorder_lot_id")) & "/ OP " & cStr(snpData("operation_seq_no")) & "</td>"
                End If
                If Not IsNull(snpData("part_id")) Then
                    strHTML = strHTML & "<td>" & cStr(snpData("part_id")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                If Not IsNull(snpData("clock_in")) Then
                    strHTML = strHTML & "<td><p align=""right"">" & cStr(snpData("clock_in")) & "</td>"
                Else
                    strHTML = strHTML & "<td>&nbsp;</td>"
                End If
                strHTML = strHTML & "</tr>" & vbCrLf

                snpData.MoveNext
            Loop

            strHTML = strHTML & "</table>" & vbCrLf
            snpData.Close

            divOutput.InnerHTML = strHTML
        Else
            divOutput.InnerText = "No Rows Returned"
        End If

        dbMyConnection.Close
    Else
        'Could Not Connect
        divOutput.InnerText = "Could Not Connect"
    End If

    Set snpData = Nothing
    Set dbMyConnection = Nothing
End Sub

Private Function Switch(strValue)
    Dim i
    Dim intOffset
    Dim intTemp
    Dim strTemp
    Dim strKey

    strKey = "OracleDatabaseDoesNotNeedToBeDifficultToUnderstand"
    intOffset = 50

    strTemp = ""
    For i = 1 To Len(strValue)
        intTemp = Asc(Mid(strValue, i, 1)) Xor Asc(Mid(strKey, Len(strValue) + i, 1)) + intOffset
        If intTemp > 255 Then
            intTemp = intTemp - 255
        End If
        strTemp = strTemp & Chr(intTemp)
    Next

    Switch = strTemp
End Function

</script>

<body id="Background">
    <div id="divOutput" style="position: absolute; top: 10px; width: 645px; height: 280px; overflow-y:auto;">&nbsp;</div> 
</body>
</html>

Settings.html (this is just a placeholder for configuration settings):

<html >
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=Unicode" />
        <title>Hello World</title>
        <style type="text/css">
        body
        {
            margin: 0;
            width: 130px;
            height: 75px;
            font-family: verdana;
            font-weight: bold;
            font-size: small;
        }
        #gadgetContent
        {
            margin-top: 20px;
            width: 130px;
            vertical-align: middle;
            text-align: center;
            overflow: hidden;
        }
        </style>
        <script type="text/jscript" language="jscript">
            // Initialize the gadget.
            function init()
            {
                var oBackground = document.getElementById("imgBackground");
                oBackground.src = "url(images/background.png)";
            }
        </script>
    </head>

    <body onload="init()">
        <g:background id="imgBackground">
            <span id="gadgetContent">Hello World!</span>
        </g:background>
    </body>
</html>

InspectorButtonUp.png:

InspectorButtonMouseOver.png:

InspectorBackground.png (Background Image for Main Web Page):

Other Backgrounds that May be Used (Note that Power Point is a great tool for creating backgrounds):
 

Resources for Developing Windows Sidebar Applications:
http://msdn.microsoft.com/en-us/library/bb456468(VS.85).aspx
http://www.microsoft.com/uk/msdn/screencasts/screencast/262/building-a-vista-sidebar-gadget-part-1-getting-started.aspx

Resources for VBScript Programming:
http://msdn.microsoft.com/en-us/library/d1wf56tt.aspx
http://www.w3schools.com/vbscript/vbscript_ref_functions.asp

Suggested Book:
CSS  The Definitive Guide” by Eric A. Meyer:
http://www.amazon.com/CSS-Definitive-Guide-Eric-Meyer/dp/0596527330





Tracking Performance Problems – Inserting a Hint into SQL in a Compiled Program

18 12 2009

December 18, 2009

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

The lead into this slide is that a report in an ERP package was running far slower than expected following an upgrade of the ERP package.  Of course the SQL statements are hard coded into the ERP package, so there is not much that can be done, right?  I created a 10046 extended SQL trace file at level 12, and then passed the trace file through my Toy Project for Performance Tuning (http://hoopercharles.wordpress.com/2009/12/13/toy-project-for-performance-tuning-2/).  One of the outputs of my program’s 10046 trace file parser provides an overview of the trace file, as well as an overview of each SQL statement.  A screen shot of that output follows:

The screen shot shows that there were 10,399 execute calls in the trace file that consumed 47.67 seconds of the server’s CPU time, and that the elapsed time/wall clock time from the server’s perspective for executing the SQL statements is 50.32 seconds for the executions.  There were 16,146 fetch calls that consumed 263.48 seconds of the server’s CPU time, and the elapsed time/wall clock time from the server’s perspective for fetching the rows for the SQL statements is 263.64 seconds.  Note that there were 0 physical reads and 8,804,970 consistent gets during the fetch (what’s that buffer cache hit ratio?).  The SQL*Net message from client wait totaled 107.33 seconds, but 42.62 seconds were in a single block of time (likely at the end of the trace file, just before tracing was disabled), so the actual total time waiting for the next request from the client is about 64.7 seconds in 22,825 round-trips.

If we scroll down a bit, we might find a couple of the greatest contributors to the server-side processing:

The screen shot shows that there are two groups of SQL statements that combined contributed to 86% of the total server-side processing time for the report.  The first SQL statement group is identified as Cursor 16 Ver 1, and the second is identified as Cursor 17 Ver 1. 

We could then search the remainder of this file to locate those identifiers.

The above screen shot shows that there are two identical SQL statements in the first group – the first SQL statement (Cursor 16 Ver 1) was parsed, but never executed.  The row source execution plan from the 10046 trace file (in the STAT lines) shows that the optimizer decided to use the index X_RECEIVABLE_3 that is on the column ENTITY_ID (there are two distinct values for this column), rather than the much more selective index on the INVOICE_ID column.  Notice the number of consistent gets and the CPU utilization for this one SQL statement that was executed 934 times.

Why was the index selected?  Would a DBMS_XPLAN help?

The DBMS_XPLAN output shows that the optimizer estimated that the X_RECEIVABLE_3 index would return a single row, when in fact it returned 66124 rows.  A problem where statistics were not collected in the last 15 years?  No.

Maybe a 10053 trace will help:

In the above we see that the predicted number of rows returned with the X_RECEIVABLE_3 index will be less than that for the other indexes, and the expected CPU resources will also be slightly less, but something is not right.  The optimizer selected not to use the primary key index on the RECEIVABLE table.  Note that the calculated cost for all three indexes is 2.

Let’s try an experiment with a NO_INDEX hint to prevent the optimizer from using the X_RECEIVABLE_3 index:

In the above, notice that the primary key index (SYS_C006885) was selected by the optimizer, that the execution time dropped from 0.31 seconds to 0.01 seconds (or less), and the number of consistent gets dropped from 1311 to 5.  A rather nice improvement, but how do we force the ERP package to not use the index without the ability to modify the program’s source code?

We could do something like this, if we assume that bind variable peeking is the source of the problem:

CREATE OR REPLACE TRIGGER LOGON_FIX_APP_PERF AFTER LOGON ON DATABASE
DECLARE
  SHOULD_EXECUTE INTEGER;
BEGIN
  SELECT DECODE(SUBSTR(UPPER(PROGRAM),1,2),'VM',1,'VF',1,0)+DECODE(INSTR(PROGRAM,'\',-1),0,0,DECODE(SUBSTR(UPPER(SUBSTR(PROGRAM,INSTR(PROGRAM,'\',-1)+1)),1,2),'VM',1,'VF',1,0)) INTO SHOULD_EXECUTE FROM V$SESSION WHERE SID=(SELECT SID FROM V$MYSTAT WHERE ROWNUM=1);
  IF SHOULD_EXECUTE > 0 THEN
    EXECUTE IMMEDIATE 'ALTER SESSION SET "_optim_peek_user_binds"=FALSE';
  END IF;
END;
/

The above logon trigger results in the following row source execution plans:

But, the above approach may be too broad as it will affect every SQL statement executed by a program that begins with the letters VM or VF.  It did help this report:

The time to fetch all rows dropped from 264 seconds to just 11 seconds, and the full report displays in just over a minute.

Let’s see if we are able to trick the optimizer into not using the X_RECEIVABLE_3 index without disabling bind variable peeking.  First, we need to enable private outlines in the current session:

ALTER SESSION SET USE_PRIVATE_OUTLINES=TRUE;

For demonstration purposes, I will explicitly tell the optimizer to use the X_RECEIVABLE_3 index when creating the outline (the exact SQL statement used by the program should be used, without the hint):

CREATE OR REPLACE PRIVATE OUTLINE P_RECEIVABLE1 ON
select /*+ INDEX(I X_RECEIVABLE_3) */ sum(a.apply_amount)
from RECV_MEMO_APPLY a, RECEIVABLE i
where a.inv_invoice_id = :1          
and a.apply_date <= :2             
and a.inv_invoice_id = i.invoice_id
and i.status != 'X'
and i.total_amount != 0 and i.recv_gl_acct_id = :3 and ENTITY_ID = :4;

Note that I had to specify a hint in this case as I was at the time testing in Oracle 11g R1 (11.1.0.6), which refused to use the X_RECEIVABLE_3 on its own to reproduce the problem seen with Oracle 10.2.0.x.

Let’s view the hints generated by the optimizer when it created the outline:

SELECT
  HINT#,
  HINT_TEXT
FROM
  OL$HINTS
WHERE
  OL_NAME='P_RECEIVABLE1';

HINT#  HINT_TEXT
    1  USE_NL(@"SEL$1" "A"@"SEL$1")
    2  LEADING(@"SEL$1" "I"@"SEL$1" "A"@"SEL$1")
    3  INDEX(@"SEL$1" "A"@"SEL$1" ("RECV_MEMO_APPLY"."INV_INVOICE_ID" "RECV_MEMO_APPLY"."MEMO_INVOICE_ID"))
    4  INDEX(@"SEL$1" "I"@"SEL$1" ("RECEIVABLE"."ENTITY_ID"))
    5  OUTLINE_LEAF(@"SEL$1")
    6  ALL_ROWS
    7  OPTIMIZER_FEATURES_ENABLE('10.2.0.2')
    8  IGNORE_OPTIM_EMBEDDED_HINTS

We can then verify that in fact the slow execution plan was selected:

EXPLAIN PLAN FOR
select /*+ INDEX(I X_RECEIVABLE_3) */ sum(a.apply_amount)
from RECV_MEMO_APPLY a, RECEIVABLE i
where a.inv_invoice_id = :1          
and a.apply_date <= :2             
and a.inv_invoice_id = i.invoice_id
and i.status != 'X'
and i.total_amount != 0 and i.recv_gl_acct_id = :3                   and ENTITY_ID = :4;

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'BASIC +NOTE'));

------------------------------------------------------------
| Id  | Operation                      | Name              |
------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |
|   1 |  SORT AGGREGATE                |                   |
|   2 |   TABLE ACCESS BY INDEX ROWID  | RECV_MEMO_APPLY   |
|   3 |    NESTED LOOPS                |                   |
|   4 |     TABLE ACCESS BY INDEX ROWID| RECEIVABLE        |
|   5 |      INDEX RANGE SCAN          | X_RECEIVABLE_3    |
|   6 |     INDEX RANGE SCAN           | X_RECV_MEMO_APP_1 |
------------------------------------------------------------

Next, we will create a stored outline that explicitly specifies not to use the X_RECEIVABLE_3 index:

CREATE OR REPLACE PRIVATE OUTLINE P_RECEIVABLE_TEMP ON
select /*+ NO_INDEX(I X_RECEIVABLE_3) */ sum(a.apply_amount)
from RECV_MEMO_APPLY a, RECEIVABLE i
where a.inv_invoice_id = :1          
and a.apply_date <= :2             
and a.inv_invoice_id = i.invoice_id
and i.status != 'X'
and i.total_amount != 0 and i.recv_gl_acct_id = :3                   and ENTITY_ID = :4;

Let’s view the hints generated by the optimizer when it created the outline:

SELECT
  HINT#,
  HINT_TEXT
FROM
  OL$HINTS
WHERE
  OL_NAME='P_RECEIVABLE_TEMP';

HINT#  HINT_TEXT
    1  USE_NL(@"SEL$1" "A"@"SEL$1")
    2  LEADING(@"SEL$1" "I"@"SEL$1" "A"@"SEL$1")
    3  INDEX(@"SEL$1" "A"@"SEL$1" ("RECV_MEMO_APPLY"."INV_INVOICE_ID" "RECV_MEMO_APPLY"."MEMO_INVOICE_ID"))
    4  INDEX(@"SEL$1" "I"@"SEL$1" ("RECEIVABLE"."INVOICE_ID"))
    5  OUTLINE_LEAF(@"SEL$1")
    6  ALL_ROWS
    7  OPTIMIZER_FEATURES_ENABLE('10.2.0.2')
    8  IGNORE_OPTIM_EMBEDDED_HINTS

We can then verify that the faster execution plan was selected:

EXPLAIN PLAN FOR
select /*+ NO_INDEX(I X_RECEIVABLE_3) */ sum(a.apply_amount)
from RECV_MEMO_APPLY a, RECEIVABLE i
where a.inv_invoice_id = :1          
and a.apply_date <= :2             
and a.inv_invoice_id = i.invoice_id
and i.status != 'X'
and i.total_amount != 0 and i.recv_gl_acct_id = :3                   and ENTITY_ID = :4;

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'BASIC +NOTE'));

-----------------------------------------------------------
| Id  | Operation                     | Name              |
-----------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |
|   1 |  SORT AGGREGATE               |                   |
|   2 |   NESTED LOOPS                |                   |
|   3 |    TABLE ACCESS BY INDEX ROWID| RECEIVABLE        |
|   4 |     INDEX UNIQUE SCAN         | SYS_C0011925      |
|   5 |    TABLE ACCESS BY INDEX ROWID| RECV_MEMO_APPLY   |
|   6 |     INDEX RANGE SCAN          | X_RECV_MEMO_APP_1 |
-----------------------------------------------------------

Next is the potentially dangerous part – refer to Metalink Notes:604022.1, 726802.1, 730062.1, 144194.1, 728647.1, 5893396.8, as well as http://www.jlcomp.demon.co.uk/04_outlines.rtf

We delete the outline hints from the outline with the slow execution plan (the one with the INDEX(I X_RECEIVABLE_3) hint in this case):

DELETE FROM
  OL$HINTS
WHERE
  OL_NAME='P_RECEIVABLE1';

Then we copy the hints from the outline for the fast execution plan (with our NO_INDEX hint):

INSERT INTO
  OL$HINTS
SELECT
  'P_RECEIVABLE1',
  HINT#,
  CATEGORY,
  HINT_TYPE,
  HINT_TEXT,
  STAGE#,
  NODE#,
  TABLE_NAME,
  TABLE_TIN,
  TABLE_POS,
  REF_ID,
  USER_TABLE_NAME,
  COST,
  CARDINALITY,
  BYTES,
  HINT_TEXTOFF,
  HINT_TEXTLEN,
  JOIN_PRED,
  SPARE1,
  SPARE2,
  HINT_STRING
FROM
  OL$HINTS
WHERE
  OL_NAME='P_RECEIVABLE_TEMP';

COMMIT;

Then we instruct Oracle to refresh the private outline:

EXEC DBMS_OUTLN_EDIT.REFRESH_PRIVATE_OUTLINE('P_RECEIVABLE1')

Now, let’s try generating the plan for the original query again (with the forced/hinted X_RECEIVABLE_3 index usage):

EXPLAIN PLAN FOR
select /*+ INDEX(I X_RECEIVABLE_3) */ sum(a.apply_amount)
from RECV_MEMO_APPLY a, RECEIVABLE i
where a.inv_invoice_id = :1          
and a.apply_date <= :2             
and a.inv_invoice_id = i.invoice_id
and i.status != 'X'
and i.total_amount != 0 and i.recv_gl_acct_id = :3                   and ENTITY_ID = :4;

SELECT
  *
FROM
  TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'BASIC +NOTE'));

-----------------------------------------------------------
| Id  | Operation                     | Name              |
-----------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |
|   1 |  SORT AGGREGATE               |                   |
|   2 |   NESTED LOOPS                |                   |
|   3 |    TABLE ACCESS BY INDEX ROWID| RECEIVABLE        |
|   4 |     INDEX UNIQUE SCAN         | SYS_C0011925      |
|   5 |    TABLE ACCESS BY INDEX ROWID| RECV_MEMO_APPLY   |
|   6 |     INDEX RANGE SCAN          | X_RECV_MEMO_APP_1 |
-----------------------------------------------------------

Note that even though we hinted/forced Oracle to use the X_RECEIVABLE_3 index, it now selected to use the primary key index SYS_C0011925 due to the hacked private outline.  Oracle IGNORED MY HINT (actually, it did exactly as the outline instructed).

Now, let’s make the outline a bit more permanent, converting it from a private outline to a public outline:

CREATE PUBLIC OUTLINE PP_RECEIVABLE1 FROM PRIVATE P_RECEIVABLE1;
ALTER SYSTEM SET USE_STORED_OUTLINES=TRUE;
DROP PRIVATE OUTLINE P_RECEIVABLE1;
DROP PRIVATE OUTLINE P_RECEIVABLE_TEMP;

If we then generate a DBMS_XPLAN for the original query (in my example, the one with the forced index hint), we see the following:

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE               |                   |      1 |      1 |      1 |00:00:00.01 |       5 |
|   2 |   NESTED LOOPS                |                   |      1 |      1 |      0 |00:00:00.01 |       5 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| RECEIVABLE        |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  4 |     INDEX UNIQUE SCAN         | SYS_C0011925      |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| RECV_MEMO_APPLY   |      1 |      1 |      0 |00:00:00.01 |       2 |
|*  6 |     INDEX RANGE SCAN          | X_RECV_MEMO_APP_1 |      1 |      1 |      0 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("I"."STATUS"<>'X' AND "I"."TOTAL_AMOUNT"<>0 AND "I"."RECV_GL_ACCT_ID"=:3 AND
              "ENTITY_ID"=:4))
   4 - access("I"."INVOICE_ID"=:1)
   5 - filter("A"."APPLY_DATE"<=:2)
   6 - access("A"."INV_INVOICE_ID"=:1)

Note
-----
   - outline "PP_RECEIVABLE1" used for this statement

But there is a catch.  The USE_STORED_OUTLINES parameter cannot be set in the init.ora or spfile, so we need a STARTUP trigger to set the parameter:

CREATE OR REPLACE TRIGGER ENABLE_OUTLINES_TRIG AFTER STARTUP ON DATABASE
BEGIN
  EXECUTE IMMEDIATE('ALTER SYSTEM SET USE_STORED_OUTLINES=TRUE');
END;

 
After implementing the stored outlines there is a new top SQL statement in the 10046 trace profile file.  But, at some point we have to stop (before the effects of compulsive tuning disorder are achieved). 

My original idea for hacking the stored outlines came from the book “Troubleshooting Oracle Performance”. 

Note that hacking stored outlines should not be the first step.  Instead, see if it is possible to modify optimizer parameters at the session level first to achieve the desired execution plan.  Once the desired execution plan is achieved, create the stored outline to freeze the execution plan.





Update Rows in Another Table with the Help of Analytic Functions

17 12 2009

December 17, 2009

In a recent comp.databases.oracle.misc Usenet thread:
http://groups.google.com/group/comp.databases.oracle.misc/browse_thread/thread/42c0d1550278250b

The following question was asked:

I need help with a query which involves the 2 tables defined below.  What I need to do is choose the record with the max “Eff Date” from “Table A” for a particular “Emp No.” and update the “Desc” from that record in the field “Desc” of “Table B” for the same “Emp No.”. I am able to choose the max “Eff Date” record for each employee from Table A but somehow not able to updated the same “Desc” in “Table B”.

Request you to please help the query. Any help would be appreciated.
Thanks!

Table A
Emp No. Group   Eff Date        Desc
1234    CI      01/01/1989      X
1234    CI      01/02/2000      X
1234    CI      01/02/2006      A
2345    AF      01/01/1990      X
2345    AF      01/02/2005      A

 

Table B
Emp No. Group   Desc
1234    CI      X
2345    AF      A
3456    CI      A

I provided the following suggestion:

Watch the query and results closely as one possible solution is built (there are other methods):

CREATE TABLE T1 (
  EMP_NO NUMBER,
  GROUPING VARCHAR2(5),
  EFF_DATE DATE,
  DESCR VARCHAR2(5));

CREATE TABLE T2 (
  EMP_NO NUMBER,
  GROUPING VARCHAR2(5),
  DESCR VARCHAR2(5));

INSERT INTO T1 VALUES (1234,'CI',TO_DATE('01/01/1989','MM/DD/YYYY'),'X');
INSERT INTO T1 VALUES (1234,'CI',TO_DATE('01/02/2000','MM/DD/YYYY'),'X');
INSERT INTO T1 VALUES (1234,'CI',TO_DATE('01/02/2006','MM/DD/YYYY'),'A');
INSERT INTO T1 VALUES (2345,'AF',TO_DATE('01/01/1990','MM/DD/YYYY'),'X');
INSERT INTO T1 VALUES (2345,'AF',TO_DATE('01/02/2005','MM/DD/YYYY'),'A');

INSERT INTO T2 VALUES (1234,'CI','XNN');
INSERT INTO T2 VALUES (2345,'AF','ANN');
INSERT INTO T2 VALUES (3456,'CI','ANN');

COMMIT;

 

SELECT
  EMP_NO,
  GROUPING,
  DESCR
FROM
  T2;

EMP_NO GROUP DESCR
------ ----- -----
  1234 CI    XNN
  2345 AF    ANN
  3456 CI    ANN

 

SELECT
  EMP_NO,
  GROUPING,
  EFF_DATE,
  DESCR
FROM
  T1;

EMP_NO GROUP EFF_DATE  DESCR
------ ----- --------- -----
  1234 CI    01-JAN-89 X
  1234 CI    02-JAN-00 X
  1234 CI    02-JAN-06 A
  2345 AF    01-JAN-90 X
  2345 AF    02-JAN-05 A

 

SELECT
  EMP_NO,
  GROUPING,
  EFF_DATE,
  ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE
DESC) RN,
  DESCR
FROM
  T1;

EMP_NO GROUP EFF_DATE          RN DESCR
------ ----- --------- ---------- -----
  1234 CI    02-JAN-06          1 A
  1234 CI    02-JAN-00          2 X
  1234 CI    01-JAN-89          3 X
  2345 AF    02-JAN-05          1 A
  2345 AF    01-JAN-90          2 X

 

SELECT
  EMP_NO,
  GROUPING,
  EFF_DATE,
  DESCR
FROM
  (SELECT
    EMP_NO,
    GROUPING,
    EFF_DATE,
    ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN,
    DESCR
  FROM
    T1)
WHERE
  RN=1;

EMP_NO GROUP EFF_DATE  DESCR
------ ----- --------- -----
  1234 CI    02-JAN-06 A
  2345 AF    02-JAN-05 A

 

UPDATE
  T2
SET
  DESCR=(
    SELECT
      DESCR
    FROM
      (SELECT
        EMP_NO,
        GROUPING,
        ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN,
        DESCR
      FROM
        T1) T1
    WHERE
      RN=1
      AND T1.EMP_NO=T2.EMP_NO
      AND T1.GROUPING=T2.GROUPING)
WHERE
  (T2.EMP_NO,T2.GROUPING) IN (
    SELECT
      EMP_NO,
      GROUPING
    FROM
      (SELECT
        EMP_NO,
        GROUPING,
        ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN,
        DESCR
      FROM
        T1)
    WHERE
      RN=1);

2 rows updated.

 

SELECT
  EMP_NO,
  GROUPING,
  DESCR
FROM
  T2;

EMP_NO GROUP DESCR
------ ----- -----
  1234 CI    A
  2345 AF    A
  3456 CI    ANN

 

Note that in the above, I assumed that the combination of EMP_NO and GROUPING had to be the same.

~~~~~~~~~~~~~~~~~~~~~~~

Maxim Demenko provided a very different approach to solving the problem that is both compact and impressive:

SQL> merge into t2 t2
   2  using (
   3    select emp_no,grouping,
   4      max(descr) keep(dense_rank last order by eff_date) descr
   5      from t1 group by emp_no,grouping) t1
   6  on (t1.emp_no=t2.emp_no
   7  and t1.grouping=t2.grouping)
   8  when matched then update set t2.descr=t1.descr
   9  ;

2 rows merged.

 

Execution Plan
----------------------------------------------------------
Plan hash value: 3235844370

-------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | MERGE STATEMENT        |      |     2 |    16 |     8  (25)| 00:00:01 |
|   1 |  MERGE                 | T2   |       |       |            |          |
|   2 |   VIEW                 |      |       |       |            |          |
|*  3 |    HASH JOIN           |      |     2 |   108 |     8  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL  | T2   |     3 |    99 |     3   (0)| 00:00:01 |
|   5 |     VIEW               |      |     5 |   105 |     4  (25)| 00:00:01 |
|   6 |      SORT GROUP BY     |      |     5 |   150 |     4  (25)| 00:00:01 |
|   7 |       TABLE ACCESS FULL| T1   |     5 |   150 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------­---

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

~~~~~~~~~~~~~~~~~~~~~~~

Since Maxim provided an execution plan, let’s compare the efficiency of the two methods with a larger test case that uses the same table definitions:

TRUNCATE TABLE T1;
TRUNCATE TABLE T2;

INSERT INTO T1
SELECT
  DECODE(MOD(ROWNUM,10),
         0,0000,
         1,1111,
         2,2222,
         3,3333,
         4,4444,
         5,5555,
         6,6666,
         7,7777,
         8,8888,
         9,9999),
  DECODE(MOD(ROWNUM,6),
         0,'AA',
         1,'BB',
         2,'CC',
         3,'DD',
         4,'EE',
         5,'FF'),
  TRUNC(SYSDATE+SIN(ROWNUM/180*3.141592)*1000),
  UPPER(DBMS_RANDOM.STRING('A',1))
FROM
  DUAL
CONNECT BY
  LEVEL<=1000000;

INSERT INTO T2
SELECT
  DECODE(MOD(ROWNUM,10),
         0,0000,
         1,1111,
         2,2222,
         3,3333,
         4,4444,
         5,5555,
         6,6,
         7,7,
         8,8,
         9,9),
  DECODE(MOD(ROWNUM,11),
         0,'AA',
         1,'BB',
         2,'CC',
         3,'DD',
         4,'EE',
         5,'FF',
         6,'GG',
         7,'HH',
         8,'II',
         9,'JJ',
         10,'KK'),
  UPPER(DBMS_RANDOM.STRING('A',3))
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

COMMIT;

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

 

SET PAGESIZE 1000
SET LINESIZE 150
ALTER SESSION SET STATISTICS_LEVEL='ALL';
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;

SPOOL C:\CHECKTIMING.TXT

UPDATE
  T2
SET
  DESCR=(
    SELECT
      DESCR
    FROM
      (SELECT
        EMP_NO,
        GROUPING,
        ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN,
        DESCR
      FROM
        T1) T1
    WHERE
      RN=1
      AND T1.EMP_NO=T2.EMP_NO
      AND T1.GROUPING=T2.GROUPING)
WHERE
  (T2.EMP_NO,T2.GROUPING) IN (
    SELECT
      EMP_NO,
      GROUPING
    FROM
      (SELECT
        EMP_NO,
        GROUPING,
        ROW_NUMBER() OVER (PARTITION BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN,
        DESCR
      FROM
        T1)
    WHERE
      RN=1);

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

SELECT
  *
FROM
  T2
WHERE
  LENGTH(DESCR)=1
ORDER BY
  EMP_NO;

ROLLBACK;
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;

merge into t2 t2
 using (
   select emp_no,grouping,
     max(descr) keep(dense_rank last order by eff_date) descr
     from t1 group by emp_no,grouping) t1
 on (t1.emp_no=t2.emp_no
 and t1.grouping=t2.grouping)
 when matched then update set t2.descr=t1.descr
 ;

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

SELECT
  *
FROM
  T2
WHERE
  LENGTH(DESCR)=1
ORDER BY
  EMP_NO;

ROLLBACK;
SPOOL OFF

What is the output of the above?

SQL_ID  8w16pv37zxuh5, child number 0
-------------------------------------
UPDATE   T2 SET   DESCR=(     SELECT       DESCR     FROM       (SELECT
        EMP_NO,         GROUPING,         ROW_NUMBER() OVER (PARTITION
BY EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN,         DESCR
FROM         T1) T1     WHERE       RN=1       AND T1.EMP_NO=T2.EMP_NO
     AND T1.GROUPING=T2.GROUPING) WHERE   (T2.EMP_NO,T2.GROUPING) IN (
   SELECT       EMP_NO,       GROUPING     FROM       (SELECT
EMP_NO,         GROUPING,         ROW_NUMBER() OVER (PARTITION BY
EMP_NO, GROUPING ORDER BY EFF_DATE DESC) RN,         DESCR       FROM
      T1)     WHERE       RN=1) 

Plan hash value: 2277482977                                                                                                                          

-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT            |          |      1 |        |      0 |00:00:06.12 |   59000 |   3282 |      5 |       |       |          |
|   1 |  UPDATE                     | T2       |      1 |        |      0 |00:00:06.12 |   59000 |   3282 |      5 |       |       |          |
|*  2 |   HASH JOIN SEMI            |          |      1 |      1 |     17 |00:00:03.06 |    3289 |   3280 |      5 |   816K|   816K| 1167K (0)|
|   3 |    TABLE ACCESS FULL        | T2       |      1 |    100 |    100 |00:00:00.03 |       7 |      6 |      0 |       |       |          |
|   4 |    VIEW                     | VW_NSO_1 |      1 |   1000K|     30 |00:00:03.03 |    3282 |   3274 |      5 |       |       |          |
|*  5 |     VIEW                    |          |      1 |   1000K|     30 |00:00:03.03 |    3282 |   3274 |      5 |       |       |          |
|*  6 |      WINDOW SORT PUSHED RANK|          |      1 |   1000K|     67 |00:00:03.03 |    3282 |   3274 |      5 | 36864 | 36864 |   25M (1)|
|   7 |       TABLE ACCESS FULL     | T1       |      1 |   1000K|   1000K|00:00:00.01 |    3275 |   3269 |      0 |       |       |          |
|*  8 |   VIEW                      |          |     17 |  16667 |     17 |00:00:03.06 |   55675 |      0 |      0 |       |       |          |
|*  9 |    WINDOW SORT PUSHED RANK  |          |     17 |  16667 |    566K|00:00:02.53 |   55675 |      0 |      0 |  1541K|   615K| 1369K (0)|
|* 10 |     TABLE ACCESS FULL       | T1       |     17 |  16667 |    566K|00:00:01.13 |   55675 |      0 |      0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):                                                                                                  
---------------------------------------------------                                                                                                  
   2 - access("T2"."EMP_NO"="EMP_NO" AND "T2"."GROUPING"="GROUPING")
   5 - filter("RN"=1)
   6 - filter(ROW_NUMBER() OVER ( PARTITION BY "EMP_NO","GROUPING" ORDER BY INTERNAL_FUNCTION("EFF_DATE") DESC )<=1)
   8 - filter("RN"=1)
   9 - filter(ROW_NUMBER() OVER ( PARTITION BY "EMP_NO","GROUPING" ORDER BY INTERNAL_FUNCTION("EFF_DATE") DESC )<=1)
  10 - filter(("EMP_NO"=:B1 AND "GROUPING"=:B2))

    EMP_NO GROUP DESCR
---------- ----- -----
         0 CC    W   
         0 EE    F   
      1111 BB    F   
      1111 DD    U   
      1111 FF    Y   
      2222 AA    W   
      2222 CC    T   
      2222 EE    K   
      3333 BB    Z   
      3333 FF    I   
      3333 DD    W   
      4444 AA    Z   
      4444 EE    G   
      4444 CC    S   
      5555 DD    L   
      5555 BB    Y   
      5555 FF    X   

 

SQL_ID  93cj2ck69n4kg, child number 0
-------------------------------------
merge into t2 t2  using (    select emp_no,grouping,      max(descr)
keep(dense_rank last order by eff_date) descr      from t1 group by
emp_no,grouping) t1  on (t1.emp_no=t2.emp_no  and
t1.grouping=t2.grouping)  when matched then update set t2.descr=t1.descr

Plan hash value: 4231777338                                                                                                                          

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT        |      |      1 |        |      1 |00:00:01.53 |    3301 |   3277 |       |       |          |
|   1 |  MERGE                 | T2   |      1 |        |      1 |00:00:01.53 |    3301 |   3277 |       |       |          |
|   2 |   VIEW                 |      |      1 |        |     17 |00:00:01.50 |    3282 |   3275 |       |       |          |
|*  3 |    HASH JOIN           |      |      1 |     43 |     17 |00:00:01.50 |    3282 |   3275 |   921K|   921K| 1181K (0)|
|   4 |     VIEW               |      |      1 |     43 |     30 |00:00:01.47 |    3275 |   3269 |       |       |          |
|   5 |      SORT GROUP BY     |      |      1 |     43 |     30 |00:00:01.47 |    3275 |   3269 | 73728 | 73728 |          |
|   6 |       TABLE ACCESS FULL| T1   |      1 |   1000K|   1000K|00:00:00.01 |    3275 |   3269 |       |       |          |
|   7 |     TABLE ACCESS FULL  | T2   |      1 |    100 |    100 |00:00:00.03 |       7 |      6 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------

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

    EMP_NO GROUP DESCR
---------- ----- -----
         0 CC    Z   
         0 EE    Z   
      1111 BB    Z   
      1111 DD    X   
      1111 FF    Z   
      2222 AA    Z   
      2222 CC    Z   
      2222 EE    Z   
      3333 BB    Z   
      3333 FF    Z   
      3333 DD    Z   
      4444 AA    Z   
      4444 EE    Z   
      4444 CC    Z   
      5555 DD    Z   
      5555 BB    Z   
      5555 FF    Z   

From the point of view of performance, Maxim’s solution is a clear winner.  It is interesting to note that the value of the DESCR column in table T2 differs for the two approaches.





Output Employee Attendance Calendar to Web with VBS

16 12 2009

December 16, 2009

This post is adapted from a small part of a presentation I gave a couple months ago.  The original code sample integrated into an ERP system to display an employee’s running attendance record for the last 6 or 12 months in graphical form, output to an Internet Explorer window.

First, we need sample “attendance” data in a table:

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

The above created a table with 1,000 rows that picked one of five employees at random for each row, specifying a random date between today and 999 days ago, with one of eight random identifiers for the date.  The data in the table will look something like this:

SELECT
  *
FROM
  EMPLOYEE_RECORD_TEST
WHERE
  ROWNUM<=10;

EMPLOYEE_ID SHIFT_DAT INDIRECT_ID
----------- --------- -----------
MIKE        03-SEP-08 OTHER
MIKE        26-JUL-09 HOL
MIKE        27-MAY-09 EXCUSE
ERIC        27-JUL-08 OTHER
ERIC        02-NOV-07 VAC
ROB         02-OCT-07 OTHER
JOE         26-MAY-08 HOL
ERIC        29-JUL-08 ABS
ERIC        23-JUL-09 MIL
ERIC        14-JUN-07 HOL

Now that we have sample data, let’s see what we are trying to achieve (reduced in size):

As the color-coded output shows, Eric took a vacation day on January 4 and March 8.  Eric also was on bereavement leave on February 23 and 24, as well as March 22 and 25.  So, how was this output created?  A VBS script connected to the Oracle database (using a custom DLL to hide the username and password, and to simplify the process of submitting the SQL statement with bind variables), submitted a query, and then built the web page on the fly.

Dim varDateStart
Dim varDateEnd
Dim varDateMonthStart
Dim varDateMonthEnd
Dim intWeekdayStart
Dim intShiftDay
Dim i
Dim intRow
Dim intCol

'The color constants
Dim lngVacationBackColor
Dim lngHolidayBackColor
Dim lngBereavementBackColor
Dim lngJuryDutyBackColor
Dim lngAbsentBackColor
Dim lngExcusedBackColor
Dim lngMilitaryBackColor
Dim lngOtherMonthBackColor

Dim lngDateBackColor(31)
Dim lngDateForeColor(31)
Dim strSQL
Dim snpData
Dim OracleSQL
Dim objIE
Dim objShell

Dim intLastMonth
Dim intOutputMonth

Dim strHTML
Dim strEmployeeID
Dim dteTransactionDate

strEmployeeID = "MIKE"
'strEmployeeID = "ROB"
'strEmployeeID = "SAM"
'strEmployeeID = "JOE"
'strEmployeeID = "ERIC"

'dteTransactionDate = CDate("January 1, 2009") 'Can specify a specific date
dteTransactionDate = Date 'Can specify the current date

'Define the colors to be used to indicate the indirect in the date
'Note that the RGB components must be specified as BGR to be compatible with HTML
lngVacationBackColor = RGB(255, 0, 0)
lngHolidayBackColor = RGB(0, 255, 0)
lngBereavementBackColor = RGB(255, 175, 0)
lngJuryDutyBackColor = RGB(33, 153, 255)
lngAbsentBackColor = RGB(0, 0, 255)
lngExcusedBackColor = RGB(0, 255, 255)
lngMilitaryBackColor = RGB(255, 0, 150)
lngOtherMonthBackColor = RGB(75, 75, 100)

Set OracleSQL = CreateObject("VMDBOracle.SQLProcessor")
Set snpData = CreateObject("ADODB.Recordset")

'Specify the start of the month based on the current transaction date - set it back to the first day of the month
varDateStart = DateAdd("m", -11, DateSerial(DatePart("yyyy", dteTransactionDate), DatePart("m", dteTransactionDate), 1))

'Finding the end of the month is a little more difficult - we add 1 month to the transaction date, find the start of that month, and subtract one day
varDateEnd = DateAdd("d", -1, DateSerial(DatePart("yyyy", DateAdd("m", 1, dteTransactionDate)), DatePart("m", DateAdd("m", 1, dteTransactionDate)), 1))

'Set the starting colors
For i = 1 To 31
    lngDateBackColor(i) = RGB(230, 230, 230) 'Off White
    lngDateForeColor(i) = RGB(0, 0, 0) 'Black
Next

strSQL = "SELECT" & vbCrLf
strSQL = strSQL & "  SHIFT_DATE," & vbCrLf
strSQL = strSQL & "  EMPLOYEE_ID," & vbCrLf
strSQL = strSQL & "  INDIRECT_ID" & vbCrLf
strSQL = strSQL & "FROM" & vbCrLf
strSQL = strSQL & "  EMPLOYEE_RECORD_TEST" & vbCrLf
strSQL = strSQL & "WHERE" & vbCrLf
strSQL = strSQL & "  SHIFT_DATE BETWEEN ? AND ?" & vbCrLf
strSQL = strSQL & "  AND INDIRECT_ID IS NOT NULL" & vbCrLf

'Specify the start of the month based on the current transaction date - set it back to the first day of the month
OracleSQL.SetParameter varDateStart, "DATE"

'Finding the end of the month is a little more difficult - we add 1 month to the transaction date, find the start of that month, and subtract one day
OracleSQL.SetParameter varDateEnd, "DATE"

If strEmployeeID <> "" Then
    strSQL = strSQL & "  AND EMPLOYEE_ID= ?" & vbCrLf
    OracleSQL.SetParameter strEmployeeID, "VARCHAR"
End If

strSQL = strSQL & "ORDER BY" & vbCrLf
strSQL = strSQL & "  SHIFT_DATE," & vbCrLf
strSQL = strSQL & "  EMPLOYEE_ID," & vbCrLf
strSQL = strSQL & "  INDIRECT_ID DESC"

OracleSQL.Sql = strSQL
Set snpData = OracleSQL.Execute

intOutputMonth = False
strHTML = ""
intRow = 0

'Shadow
strHTML = strHTML & "<div style=""position: absolute; width: 200px; height: 20px;"
strHTML = strHTML & "top: 7px;left: 7;"
strHTML = strHTML & "font-family: Arial; font-size: 18pt; color: #000000;"
strHTML = strHTML & "background-color: #FFFFFF;"">"
strHTML = strHTML & "<b>" & strEmployeeID & "</b></div>" & vbCrLf

strHTML = strHTML & "<div style=""position: absolute; width: 200px; height: 20px;"
strHTML = strHTML & "top: 5px;left: 5;"
strHTML = strHTML & "font-family: Arial; font-size: 18pt; color: #FFFF00;"">"
strHTML = strHTML & "<b>" & strEmployeeID & "</b></div>" & vbCrLf

strHTML = strHTML & "<div style=""position: absolute; width: 200px; height: 20px;"
strHTML = strHTML & "top: 6px;left: 6;"
strHTML = strHTML & "font-family: Arial; font-size: 18pt; color: #0000FF;"">"
strHTML = strHTML & "<b>" & strEmployeeID & "</b></div>" & vbCrLf

If Not (snpData Is Nothing) Then
    Do While Not (snpData.EOF)
        intShiftDay = DatePart("d", CDate(snpData("shift_date")))
        Select Case CStr(snpData("indirect_id"))
            Case "VAC", "VACB", "VACC", "VACF", "VACM"
                lngDateBackColor(intShiftDay) = lngVacationBackColor
            Case "HOL", "HOLC", "HOLF", "HOLB", "HOLM"
                lngDateBackColor(intShiftDay) = lngHolidayBackColor
            Case "BEREAVE", "BEREAVEC", "BEREAVEF", "BEREAVEB", "BEREAVEM"
                lngDateBackColor(intShiftDay) = lngBereavementBackColor
            Case "JURY", "JURYC", "JURYF", "JURYB", "JURYM"
                lngDateBackColor(intShiftDay) = lngJuryDutyBackColor
            Case "ABS", "ABSC", "ABSF", "ABSB", "ABSM"
                lngDateBackColor(intShiftDay) = lngAbsentBackColor
            Case "EXCUSE", "EXCUSEC", "EXCUSEF", "EXCUSEB", "EXCUSEM"
                lngDateBackColor(intShiftDay) = lngExcusedBackColor
            Case "MIL", "MILC", "MILF", "MILB", "MILM"
                lngDateBackColor(intShiftDay) = lngMilitaryBackColor
        End Select

        'See if the month will change
        intLastMonth = DatePart("m", CDate(snpData("shift_date")))
        varDateMonthStart = DateSerial(DatePart("yyyy", CDate(snpData("shift_date"))), DatePart("m", CDate(snpData("shift_date"))), 1)
        varDateMonthEnd = DateAdd("d", -1, DateAdd("m", 1, DateSerial(DatePart("yyyy", CDate(snpData("shift_date"))), DatePart("m", CDate(snpData("shift_date"))), 1)))

        snpData.MoveNext

        intOutputMonth = False

        If snpData.EOF Then
            intOutputMonth = True
        Else
            If DatePart("m", CDate(snpData("shift_date"))) <> intLastMonth Then
                intOutputMonth = True
            End If
        End If

        If intOutputMonth = True Then
            intWeekdayStart = Weekday(varDateMonthStart)

            intRow = intRow + 1
            strHTML = strHTML & "<div style=""position: absolute; width: 200px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(1 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 14pt; color: #110011;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "<b>" & MonthName(DatePart("m", varDateMonthStart)) & " " & CStr(DatePart("yyyy", varDateMonthStart)) & "</b></div>" & vbCrLf

            intRow = intRow + 1
            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(1 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Sun</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(2 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Mon</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(3 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Tue</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(4 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Wed</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(5 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Thu</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(6 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Fri</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25 + 10) & "px;left: " & CStr(7 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #4400FF;"
            strHTML = strHTML & "background-color: #FFFFFF;"">"
            strHTML = strHTML & "Sat</div>" & vbCrLf

            intRow = intRow + 1
            'Fill in the days from the previous month
            For i = 1 To intWeekdayStart - 1
                strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
                strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(i * 25) & ";"
                strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #FFFFFF;"
                'Pad with leading 0s
                strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngOtherMonthBackColor)), "0") & Hex(lngOtherMonthBackColor) & ";"">"
                strHTML = strHTML & DatePart("d", DateAdd("d", -(intWeekdayStart - i), varDateMonthStart)) & "</div>" & vbCrLf
            Next

            For i = 1 To DatePart("d", varDateMonthEnd)
                'See if we need to jump to the next row
                If i > 1 Then
                    'See if the week day is less than the previous week day - if so, jump to the next row in the calendar since the week changed
                    If Weekday(DateAdd("d", i - 1, varDateMonthStart)) < Weekday(DateAdd("d", i - 2, varDateMonthStart)) Then
                        intRow = intRow + 1
                    End If
                End If
                intCol = Weekday(DateAdd("d", i - 1, varDateMonthStart))

                strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
                strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(Weekday(DateAdd("d", i - 1, varDateMonthStart)) * 25) & ";"
                strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #" & String(6 - Len(Hex(lngDateForeColor(i))), "0") & Hex(lngDateForeColor(i)) & ";"
                strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngDateBackColor(i))), "0") & Hex(lngDateBackColor(i)) & ";"">"
                strHTML = strHTML & CStr(i) & "</div>" & vbCrLf
            Next

            'Finish out the final week
            For i = Weekday(varDateMonthEnd) + 1 To 7
                strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 20px;"
                strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(i * 25) & ";"
                strHTML = strHTML & "font-family: Arial; font-size: 8pt; color: #FFFFFF;"
                strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngOtherMonthBackColor)), "0") & Hex(lngOtherMonthBackColor) & ";"">"
                strHTML = strHTML & CStr(i - Weekday(varDateMonthEnd)) & "</div>" & vbCrLf
            Next

            intRow = intRow + 1
            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(1 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngVacationBackColor)), "0") & Hex(lngVacationBackColor) & ";"">"
            strHTML = strHTML & "VAC</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(2 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngHolidayBackColor)), "0") & Hex(lngHolidayBackColor) & ";"">"
            strHTML = strHTML & "HOL</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(3 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngBereavementBackColor)), "0") & Hex(lngBereavementBackColor) & ";"">"
            strHTML = strHTML & "BER</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(4 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngJuryDutyBackColor)), "0") & Hex(lngJuryDutyBackColor) & ";"">"
            strHTML = strHTML & "JUR</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(5 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngAbsentBackColor)), "0") & Hex(lngAbsentBackColor) & ";"">"
            strHTML = strHTML & "ABS</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(6 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngExcusedBackColor)), "0") & Hex(lngExcusedBackColor) & ";"">"
            strHTML = strHTML & "EXC</div>" & vbCrLf

            strHTML = strHTML & "<div style=""position: absolute; width: 20px; height: 12px;"
            strHTML = strHTML & "top: " & CStr(intRow * 25) & "px;left: " & CStr(7 * 25) & ";"
            strHTML = strHTML & "font-family: Arial; font-size: 7pt; color: #000000;"
            strHTML = strHTML & "background-color: #" & String(6 - Len(Hex(lngMilitaryBackColor)), "0") & Hex(lngMilitaryBackColor) & ";"">"
            strHTML = strHTML & "MIL</div>" & vbCrLf

            'Reset the starting colors
            For i = 1 To 31
                lngDateBackColor(i) = RGB(230, 230, 230) 'Off White
                lngDateForeColor(i) = RGB(0, 0, 0) 'Black
            Next
        End If
    Loop

    snpData.Close
End If

'Fire up Internet Explorer
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Left = 0
objIE.Top = 0
objIE.Width = 260
objIE.Height = 700
objIE.StatusBar = False
objIE.MenuBar = False
objIE.Toolbar = False

objIE.Navigate "about:blank"
objIE.Document.Body.InnerHTML = strHTML
objIE.Document.Title = "Attendance " & strEmployeeID
objIE.Visible = True

'with the help of custom program, set a 1 second delay, then force the Attendance web page to the top
Set objShell = CreateObject("WScript.Shell")
objShell.Run("C:\BringToTop.exe " & Chr(34) & "Attendance " & strEmployeeID & Chr(34) & " 1")

Set objShell = Nothing
Set snpData = Nothing
Set objIE = Nothing
Set OracleSQL = Nothing

While the above uses a custom DLL for the database connection, a standard ADO connection will work just as well.  The script also uses a custom program that I created called BringToTop that simply addresses the “pop under” behavior on Vista and Windows 7.

So, which employee has the best attendance record?








Follow

Get every new post delivered to your Inbox.

Join 142 other followers